Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2015
    Posts
    51

    Unanswered: Expression Field Data Type: getting data type mismatch

    I have a main table that has three fields Route, day and Timepoint, These are all short text fields.

    In my query, I have a field with the expression as follows
    RouteCode: [route] & [Day] & Replace([Timepoint],":","")

    The result is all three fields combined with out the ":".

    I have another query in which I need to join the date and the routecode to another table to return a recordID number.

    But i am getting a datatype mismatch, the routecode in the second table i need to join with is also a short text field.

    Do fields change the data type in expressions?

    how do I fix this to get my query working?

    any ideas here?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Datatypes are?
    Table/column design is?
    Query that is faulting is?
    Sample data is?

    ...its not clever to use the wrong datatype for storing data. A time value shoukd be stored as a date column NOT text.

    What steps have you taken to diagnose which column is causing the error
    Have you checked the datatypes of all the columns match
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2015
    Posts
    51
    logtable:
    Date: date/time
    Route: String
    Day: String
    Timepoint: String ( not an actual time, and needs to have 4 digits 00:00, date/time fields drop the first 0 when a timepoint only has 3 digits like 9:30)

    RouteDetails table:
    Date: date/time
    Routecode: string
    uniqueID: string


    Query1:

    * all fields from Logtable, except route, Day and Timepoint are combined into one field. RouteCode: [route] & [Day] & Replace([Timepoint],":","")



    Query2:

    SELECT TitleVI_Q.Date, TitleVI_Q.Time, TitleVI_Q.RouteCode, TitleVI_Q.[Down: Yes/No], TitleVI_Q.[Down By], TitleVI_Q.Reason, TitleVI_Q.Details
    FROM TitleVI_Q INNER JOIN (Routes INNER JOIN RouteDetails ON Routes.UniqueID = RouteDetails.UniqueID) ON (TitleVI_Q.RouteCode = RouteDetails.RouteCode) AND (TitleVI_Q.Date = RouteDetails.Date);



    The trouble is with the Routecode from query TitleVI_Q and the routecode from RouteDetails, even though both are string, I continue to have the type mismatch error.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    TitleVI_Q is?
    Eliminate all columns but retain the join and then rebuild the query till it fails

    ..please dont say you actually have a column called down: yes/no.
    Thats so wrong onso many levels
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2015
    Posts
    51
    The query fails when TitleVI_Q.RouteCode and RouteDetails.RouteCode are joined.

    I have checked all my main tables and the data types match.

    Is there a way to set the expression field to a string in the titleVI_q query?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what is the SQL that comprises titleVI_q
    You can expressly state the datatype of a dynamic columm in a query using cstr or similar
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Table routes design is?

    There are too many sources of error in this. Id strongly recommend using a more gentle approach
    Prove 1 join works
    Prove all columns required from those tables work
    Eliminate any non critical columns for now
    Eliminate any parameters

    ...this is basic debugging. Assume nothing, dont oresume prove. Data mismatch means something in the query is not of the datatype expected. So you need to go through each element and prove its what you expect or require it to be
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by andromeda00 View Post

    I have checked all my main tables and the data types match
    That Datatype wouldn't happen to be AutoNumber, would it?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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