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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,092
Office Version
365
Platform
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
54,092
Office Version
365
Platform
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
75,732
Office Version
365
Platform
Windows
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
75,732
Office Version
365
Platform
Windows
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
75,732
Office Version
365
Platform
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 :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,021
Messages
5,508,857
Members
408,697
Latest member
Jay_Sks

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top