Error - Data type mismatch in criteria expression

excel-rob

Board Regular
Joined
Feb 24, 2009
Messages
58
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,535
Office Version
  1. 365
Platform
  1. Windows
So are year and JulianDate variables that you have calculated somewhere? If so, you should change year to something else, like myYear. Year is a reserved word (a function). Using it as a variable may confuse Excel and cause problems.
 

excel-rob

Board Regular
Joined
Feb 24, 2009
Messages
58
They are the fields in a linked table that I have no control over. I have used these fieldnames successfully in other queries due to them being in linked tables and it's worked fine though, so I think it's unlikely that's causing the problem :confused:
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,535
Office Version
  1. 365
Platform
  1. Windows
The DateSerial function is expecting (year, month, day).
I don't think the day value can exceed 31, and a Julian Date value can often exceed 31.

If you are trying to convert the Julian Date (which is probably just a number) to a Date, maybe something like this would work:

DateSerial(year,1,1)+JulianDate-1

This would start on January 1st, and add the number of days from JulianDate to it, and subtract one (since you are starting on day 1).
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

DateSerial will accept day values over 31 and it does seem to work using the Julian day system.

A type mismatch could be caused by something else, it could be a problem with the data types of year and JulianDay.

DateSerial would expect numbers.

I'm not quite sure what you mean here.
the expression has the same function on both sides of the comparison operator
With this sort of expression I would recommend a liberal sprinkling of parentheses and perhaps a conversion function or two, eg Val.:)
 

excel-rob

Board Regular
Joined
Feb 24, 2009
Messages
58
Year and JulianDay data types are integer.


Sorry, I meant to say that the expression has the same datatype on both sides of the comparison operators:
Code:
where dateserial(year,1,JulianDay) >= #26/07/2010# and dateserial(year,1,JulianDay) <= #01/08/2010#
was typing without thinking, always dangerous!

What do you mean by conversion function? I've tried putting the dates on the right hand side into dateserial functions as well, i.e. dateserial(2010,7,26) for the first one, but that gives exactly the same error, which really makes no sense to me! It is then even the same function, dateserial, on both sides of the comparison operator, which must return the same datatype surely!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The thing is the comparison/logic operators might not be using the correct data types.

In fact it is possible that they aren't doing what you expect to do.

Let me try and explain, probably badly as usual.

The And might actually be being read as #26/07/2010# AND dateserial(year,1,JulianDay).

That might not be a problem in itself because it's 2 dates but the result will be a boolean value TRUE/FALSE, in this case it will always be TRUE.

So if we replace the result in the overall expression we might have.

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

I'm not sure how that might be interpreted.

To avoid this you could try this.

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

Adding the parentheses should make sure that the expression within them is evaluated first, then the AND is evaluated.

That's all about clear as that mud BP was using in the Gulf of Mexico but it might be worth considering.

As for the conversion functions, they are just a set of functions that change data types.

The relevance here could be that your year and JulianDay field are text but DateSerial needs them to be numberic.

This might not be a problem as a lot of the time the text will automatically be converted to a number.

If it's not then you can use Val or some other function to coerce to numeric.

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

This could be total nonsense but it might be worth trying.

PS It might actually help to know where you are using the expression.:)
 

excel-rob

Board Regular
Joined
Feb 24, 2009
Messages
58
Makes perfect sense and excellently explained, thank you.

I think the latter is unlikely, as the datatypes are integer I'm 99.9% sure. I don't have the code in front of me to check, but will do so tomorrow.

The former sounds like a very good possibility.

Where would I find a list of the evaluation of operators in VBA, seems strange that the AND would be evaluated before the comparison operator, but there are many strange things I have found already in my limited forays into VBA!

Many thanks for your help.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows
What you want to look for is 'operator precedence' and I think you'll find that VBA doesn't really stray away from accepted standards.

It might not actually be the root of the problem but you might find adding a few extra parenthese can help - sometimes it can make expressions easier to follow.:)
 

excel-rob

Board Regular
Joined
Feb 24, 2009
Messages
58
So, it turns out it was due to a blank line in the linked table and no amount of if isnull() or nested queries would deal with it, it always gave the same unhelpful error, so I had to get the original linked table altered. What a pain, and why no sensible error message!

Thanks to everyone for their help :)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,823
Messages
5,655,475
Members
418,205
Latest member
jacc99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top