SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2004
    Location
    ny
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Ms Access - Data type mismatch

    Hi all,
    I am not getting the correct data when use my simple ms access query ... I mean lets say I am looking for 2 (which is a number ) within the records... The result returns the sample " 53, 23, 24, 4, 43, 56, 55, 44, 59 "... Why is that ???
    FindRouteID is Autonumber and Route_knowledge is text in ms access... Thanks all.
    Code:
    Set GetOnCalls = conn.execute("select * from realschedule where (Vacation <> 'Yes' or Vacation is null) And Training ='No' And (sch_route ='On call 2 - pm' or sch_route ='On call 2 - am' or sch_route ='On call 1 - pm' or sch_route ='On call 1 - am' or sch_route ='On call 1 - pm weekend' or sch_route ='On call 1 - am weekend') and sch_Day = '"&trim(RunDays(i))&"' and right(Sch_time_start,2) ='"&right(xRouteStart,2)&"' And Driver_ID in (select ID from drivers where instr(Route_knowledge,'"&cstr(FindRouteID)&"')) order by Driver_fn,Driver_ln")

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    table layouts please

    sample rows please

    you do not appear to be "looking for 2 (which is a number ) within the records"

    you appear to be looking for "(Vacation <> 'Yes' or Vacation is null) And Training ='No' And (sch_route ='On call 2 - pm' or sch_route ='On call 2 - am' or sch_route ='On call 1 - pm' or sch_route ='On call 1 - am' or sch_route ='On call 1 - pm weekend' or sch_route ='On call 1 - am weekend') and sch_Day = '"&trim(RunDays(i))&"' and right(Sch_time_start,2) ='"&right(xRouteStart,2)&"' And Driver_ID in (select ID from drivers where instr(Route_knowledge,'"&cstr(FindRouteID)&"'))"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2004
    Location
    ny
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Realschedule(table)
    -------------
    Vacation as text
    Training as text
    sch_route as text
    Sch_time_start as short Date
    Sch_Day as text
    Driver_ID as number

    Drivers(Table)
    ----------------
    Route_knowledge as text
    ID as auto number

    Note: FindRouteID is a number... The above query is inside a query loop...
    A sample row will be :
    Code:
     (Vacation <> 'Yes' or Vacation is null) And Training ='No' And (sch_route ='On call 2 - pm' or sch_route ='On call 2 - am' or sch_route ='On call 1 - pm' or sch_route ='On call 1 - am' or sch_route ='On call 1 - pm weekend' or sch_route ='On call 1 - am weekend') and sch_Day = '"&trim(RunDays(i))&"' and right(Sch_time_start,2) ='AM' And Driver_ID in (select ID from drivers where instr(Route_knowledge,3))

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i don't understand two things: why you have a loop, and what INSTR is doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2004
    Location
    ny
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The idea is : it loops thru routes then my query select the drivers who know that route... basic idea... If you have better idea to implement the query, please feel free...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    uh oh, i think i see what's going on

    you have a comma-delimited list of values in a single column?

    i would advise you to redesign this

    do a search on first normal form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if route_knowledge a delimited string? like "52,43,11" or "52 43 11"?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  8. #8
    SitePoint Evangelist
    Join Date
    Jun 2004
    Location
    ny
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have created lost of file... next time i will normalize... Meantime, what will I do ? it is delimited with comma...

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, now i understand your original question

    the reason it finds '2' inside '53, 23, 24, 4, 43, 56, 55, 44, 59 ' is because '2' is actually in there

    you will have to rewrite your INSTR to search for ',2,' (note the commas) inside CONCAT(',',REPLACE(Route_knowledge,' ',''),',')

    REPLACE removes all spaces, while CONCAT adds commas to the front and back
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    oh wait, you said ms access

    okay, change REPLACE and CONCAT to the ms access equivalents

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist
    Join Date
    Jun 2004
    Location
    ny
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it gonna smth like this ????

    select ID from drivers where instr((',' + replace(Route_knowledge,' ','') + ','),3))

  12. #12
    SitePoint Evangelist
    Join Date
    Jun 2004
    Location
    ny
    Posts
    560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This works ....
    select ID,driver_fn,route_knowledge from drivers where instr((',' + replace(Route_knowledge,' ','') + ','),',2,')

    When I use the above query in ms access... it is fine but when I use in asp file, it says, " Undefined function replace "

    What is going on ?
    Last edited by emmim44; May 1, 2008 at 13:07.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •