Data type mismatch in criteria expression (1 Viewer)

[BJS]

I am getting the error "Data type mismatch in criteria expression" when I put sorting on the following field in a query:

3 Month Date: DateAdd("m",+3,[Current Date])

If I remove sorting the query works fine. When I add sorting to this column in the query design grid, I get the error message.

Any ideas?

Thanks,
BJS

 

[WayneRyan]

BJS,

I can't replicate the problem. It works fine for me.

Wayne

 

[BJS]

Hi Wayne...thanks for looking at this!

I should have mentioned that the "Current Date" field in the design grid of the query looks like this:

Current Date: CDate(Format([CUR_MON],"00") & "/" & Format([CUR_DAY],"00") & "/" & Format([CUR_YR],"00"))

I can get the same error message when sorting on this field. I can sort on the "Current Date" field if I remove "CDate", but still am unable to sort on the "3 Month Date" field. I need to convert the data to Date so I can sort the records by date in the report which uses the query. Even if I do the sorting in the report, I get the same error.

Hope this helps.

 

[mresann]

I don't have a problem either. Can you post the whole query?

 

[bvan]

Some of the date functions (DateVal for example) choke when they hit nulls. See if your query is encountering any nulls in the date field. The result of a null is a type mismatch error.

 

[modest]

I don't have a problem either. Can you post the whole query?

I agree, please post the SQL

 

[BJS]

I checked the data, there are no null values in the "Current Date" field.

I would post a sample of the db with the query, but it uses ODBC connection to DB2 tables. Here is a sample of the sql:

SELECT Table1.NAME, Table1.EMP_NO, CDate(Format([CUR_MON],"00") & "/" & Format([CUR_DAY],"00") & "/" & Format([CUR_YR],"00")) AS [Current Emp Date]
FROM Table1 INNER JOIN Table2 ON Table1.KEY = Table2.KEY
ORDER BY CDate(Format([CUR_MON],"00") & "/" & Format([CUR_DAY],"00") & "/" & Format([CUR_YR],"00"));

I checked for null values; there are none in the "Current Date" field.
I am starting to think that it is an issue with the data though; just need to find out which record.

Please let me know if the SQL looks good. Thanks!

 

[FoFa]

Why not just ORDER BY [CUR_YR], [CUR_MON], [CUR_DAY]
Would not that be the same as a date sort without all the conversion?

 

[BJS]

Thank you everyone! The problem was an invalid date (09/31/2004) in one of the records. The month of September only has 30 days.

Problem solved!