Error - Data type mismatch in criteria expression

[excel-rob]

Hi,

I'd really appreciate any help with this as the above error from a query has been driving me nuts! I have a query to pull data from a table that includes this criteria expression:

where dateserial(year,1,JulianDay) >= #26/07/2010# and dateserial(year,1,JulianDay) <= #01/08/2010#

because the data in the table I am querying has dates in the form of year and Julian Day, i.e. number of days from the start of the year with no month value. The dateserial expression works fine and comes up with the right date. However, I always get the error given in the topic title above.

I've tried changing the order of the date I'm comparing to so that it's in mm/dd/yyyy format. I've tried changing it to dateserial(2010,07,26) for the first and dateserial(2010,08,01) for the second and I always get the same error.

How on Earth can there be a data type mismatch in the criteria expression when the expression has the same function on both sides of the comparison operator, that function by definition returns the same return type!!

Many thanks in advance for any help.

 

[Norie]

You really need to be careful when dealing with Null values in a database.

Some will easily deal with it, eg convert it to something sensible.

In others errors like you received or similar will come up.

It's hard to tell without knowing what database(s) you are dealing with how things will happen, or the best way to deal with them.

It being a linked table could also add to the problems.

How did you try IsNull?

Did you try Nz?

One way you could deal with this sort of thing would be to replace the null values with some other appropriate value, something like the string N/A perhaps.