none
Data type mismatch in criteria expression RRS feed

  • Question

  • Hi, I'm selecting from a table using date criteria. The query is:

    SELECT Equipment.Description, Equipment.SerialNo, Equipment.Last_Cal, Equipment.Cal_Interval
    FROM Equipment

    WHERE (((DateAdd("m",([Cal_Interval])-1,[Last_Cal]))<Date()));

    Cal_Interval is type Number

    Cal_Interval is type Date

    The main SELECT without the criteria works OK, and the DateAdd function also works OK if I use it to calculate a value in a Form. So there would seem to be no Type Mismatch. There is obviously wrong with the way that I am forming the criteria, but I just can't see. If anyone can help it would be much appreciated.

    Thursday, November 7, 2019 8:19 PM

All replies

  • You meant: Last_Cal is type Date

    I get dizzy from all the parentheses, but there is nothing fundamentally wrong with the expression.

    You could simplify it temporarily:
    DateAdd("m", 1, #11/07/2019#)

    and go from there.


    -Tom. Microsoft Access MVP

    Thursday, November 7, 2019 9:07 PM
  • Hi JFryer,

    I can't explain why it works in your Form but the Query is missing a criteria in the WHERE clause.

    Besides what Tom wrote, check parentheses.

    The WHERE clause I assumed without error or typos .....

    WHERE (((DateAdd("m",([Cal_Interval])-1,[Last_Cal]))<Date()));

    what is missing, is ....

    WHERE ([Cal_Interval] = ((DateAdd("m",([Cal_Interval])-1,[Last_Cal]))<Date()));

    Note that I'm not correcting any errors that might appear here.

    Friday, November 8, 2019 2:46 AM
  • You probably have Null values. Try:

    SELECT 
        Equipment.Description, 
        Equipment.SerialNo, 
        Equipment.Last_Cal, 
        Equipment.Cal_Interval
    FROM 
        Equipment
    WHERE 
        DateAdd("m", [Cal_Interval] - 1, Nz([Last_Cal], Date())) < Date();
    
    ' Or even this:
    
    WHERE 
        DateAdd("m", Nz([Cal_Interval], 0) - 1, Nz([Last_Cal], Date())) < Date();
    


    Gustav Brock

    Friday, November 8, 2019 9:53 AM
  • Thanks Tom,

    I ended up replacing all the Date_types, but still come up with the same mismatch error.

    If I replace [Cal_Interval] (type Number) with an actual number then everything works great. But I need to be able to pass that value in from the db. I thought it might be the calculation, so just left the Field on its own, but still get a mismatch. 

       WHERE DateAdd("m",5-1,#07/07/2019#)<Date();

       WHERE DateAdd("m",5-1,[Last_Cal])<Date();

    Both work

       WHERE DateAdd("m",[Cal_Interval],#07/07/2019#)<Date(); 

    Doesn't

    It's feels like that second argument should be some other type., but I'm not clear what.

    Saturday, November 9, 2019 3:48 PM
  • The behaviour you are experiencing supports Gustav's conclusion that the Cal_Interval column contains one or more NULLs.  What is the value of the column's Required property?  If it's False (No) then call the NZ function as Gustav suggested to return a zero.

    Ken Sheridan, Stafford, England

    Saturday, November 9, 2019 4:44 PM
  • Thanks Gustav,

    I'm afraid that made no difference, but a great tip to check for Nulls. I'll add this in. Cheers.

    Saturday, November 9, 2019 5:06 PM
  • Thanks Tom,

    I ended up replacing all the Date_types, but still come up with the same mismatch error.

    If I replace [Cal_Interval] (type Number) with an actual number then everything works great. But I need to be able to pass that value in from the db. I thought it might be the calculation, so just left the Field on its own, but still get a mismatch. 

       WHERE DateAdd("m",5-1,#07/07/2019#)<Date();

       WHERE DateAdd("m",5-1,[Last_Cal])<Date();

    Both work

       WHERE DateAdd("m",[Cal_Interval],#07/07/2019#)<Date(); 

    Doesn't

    It's feels like that second argument should be some other type., but I'm not clear what.

    You are confused. The two arguments must be of data type Number and DateTime respectively, and then the function can't possible fail for the sample data you have provided.

    The only reason for a type mismatch is, if either argument (field value) should hold a Null value, which I showed you how to correct for.

    So, double-check your values again.


    Gustav Brock

    Saturday, November 9, 2019 5:33 PM