Results 1 to 5 of 5
  1. #1
    broecher is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Sep 2010
    Posts
    38

    Angry data type mismatch in criteria expression

    I am trying to query by date and I keep getting this error (data type mismatch in criteria expression). I have read a lot of posts about date format and I don't see any problems with that. I think it might not like something about the way the date was created in another query possibly.



    The original date format was not recognizable by access: "Wed Jan 20 21:01:31 2010". I used a long and drawn out expression that converts Jan to 01, and extracted the day, year, etc into their own fields. Then I used DateSerial to put the numbers back together into "01/20/2010".

    In another query, I reference this reconstructed date and it displays correctly. But when I try to show records after a certain date using Criteria in design mode, for example >#08/08/2010# , I get the error.

    After changing the format several times in both this query and the other referenced queries, I tried something else. I converted the dates to numbers, expr1: CDbl([Date]) . Now I have just numbers like 40427. They appear right justified like a number should. I try to show records after a certain number, for example >40000 , I get the same error...

    When I go back to the original query that constructs this date, I cannot use any criteria on the date here either. Here I get a different error. It says Enter Parameter Value for my month field which is the super long IIf expression. This probably doesnt matter, but if I enter a value, the querry does display for a momment, then I get the original error message and the query turns into all #name?

    Then when I try to add Criteria to the field with the month expression, I get an expression too complex error.

    Any suggestions at what I should do differently?

    Here is the month expression:

    fxMonth: IIf(Left((Right([first fix LMT],20)),3)="Jan",1,IIf(Left((Right([first fix LMT],20)),3)="Feb",2,IIf(Left((Right([first fix LMT],20)),3)="Mar",3,IIf(Left((Right([first fix LMT],20)),3)="Apr",4,IIf(Left((Right([first fix LMT],20)),3)="May",5,IIf(Left((Right([first fix LMT],20)),3)="Jun",6,IIf(Left((Right([first fix LMT],20)),3)="Jul",7,IIf(Left((Right([first fix LMT],20)),3)="Aug",8,IIf(Left((Right([first fix .............
    Last edited by broecher; 09-09-2010 at 02:06 PM. Reason: aditional info

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    you were trying to compare a string/text with a date. That's why Access told you (data type mismatch in criteria expression).

    it's not very long to convert "Wed Jan 20 21:01:31 2010" into a date, you don't need to convert Jan to 01:
    cdate(mid(fieldname,5,7)+right(fieldname,4))

  3. #3
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    From what you've said, it sounds like either:

    • Your data isn't in an actual Date/Time format (maybe it's stored as Text and just looks like a date?), or
    • The data is being pulled from a Query (yes, you can base a Query off of another Query) that is converting the field to another format.

    If you have the first problem:
    I'd STRONGLY recommend changing the format in your Table to an actual Date/Time Format. This makes things MUCH easier to Calculate or to Filter off of.

    If you have the second problem:
    In any query where you modify the date, make sure to use either Access's builtin Date functions (DateDiff, DateSerial, DatePart, etc) or enclose it in DateValue (example DateValue(<Expression here>)).

    If you still can't figure out the source of your problem, just post the Table's format and your Query here and we'll be happy to take a look at it.

    EDIT: Or you can just use DateValue(Format([Fieldname], "Short Date")). Access should be able to convert just about any Date format for you automatically.
    Last edited by Rawb; 09-09-2010 at 02:10 PM. Reason: lol simultaneous posts!

  4. #4
    broecher is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Sep 2010
    Posts
    38

    Smile

    Thanks a million!
    I used
    cdate(mid(fieldname,5,7)+right(fieldname,4))
    I don't know a lot of this syntax yet so this was a big help.

    After replacing the long expression, I got an invalid use of null. I realized that the criteria I was attempting to use was being applied to records that were hidden but contained an error. I used an IIf to fix that problem:

    correctedDate: IIf([fieldX] Is Null,0,CDate(Mid([originalDate]![first fix LMT],5,7)+Right([originalDate]![first fix LMT],4)))

    Now all those hidden rows say 12/30/1899, but they are hidden so I am happy...

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    We are all happy that you can work it out.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data type mismatch in criteria expression
    By shexe in forum Queries
    Replies: 2
    Last Post: 09-01-2010, 12:47 PM
  2. Type mismatch
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 08-07-2010, 06:54 AM
  3. data type mismatch
    By jgelpi16 in forum Programming
    Replies: 5
    Last Post: 08-02-2010, 04:15 PM
  4. Type mismatch in expression /Query
    By Brian62 in forum Queries
    Replies: 4
    Last Post: 04-22-2010, 10:00 AM
  5. Replies: 4
    Last Post: 10-26-2009, 05:27 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums