Data Type Mismatch in Criteria expression in MSQuery but not Access

[anasttin]

Hi,

I have a query that seems to work fine in MS Access 2007-10 but when I use MS Query to bring it into Excel 2010, I get the error "Data type Mismatch in Criteria Expression".

Can anyone suggest why as it's causing me all sorts of grief.

Many thanks in advance.

[Micron]

Are you using an MS query in Excel against a worksheet and some of the cells contain no data? My first guess is that it isn't dealing with Nulls (cells with no values). Another guess would be that in Access, all the data types for the query are correctly defined, so it works there. However, on the Excel side you're trying to do something with or against one data type when it is something else (e.g. you're trying to perform an aggregate function on data that looks numeric but is in fact text). Not much info in your post, so guesses are all I have.

[anasttin]

Hi Micron,


I'm not sure what you mean by "against a worksheet".


I'm bringing in a simple query which only gets data from one table in the access database.


It does round some fields to 2 decimal places though.


I liked your idea of nulls possibly being the problem so added the nz() function within my rounding formulas. The query still works fine in Access, but when I try to add it into a blank excel sheet, I get the message "undefined function nz in expression".


Any other thoughts?


Thanks for your help.

[Micron]

That is a new error. I believe the Nz function is specific to Access. You'd have to write a custom function or try using an If expression.
As for the old error, I would copy the workbook, open the MS query (I presume you saved it) and remove one field at a time from the query and run it. When the error goes away, you should have isolated the problem field. As previously mentioned, the original error means you are trying to handle a type of data that doesn't conform. That could be nulls in the source data, text you are trying to sum (or other aggregate functions on data types that can't be done), fields with objects in them, or here's a far out one - an Access field is multi value. I have no idea how Excel could handle that.

[anasttin]

Appreciate the ongoing support Micron.

I only used the NZ function within Access, not Excel.

Still, I tried replacing the NZ function with iif(isnull(...

Now I no longer have the NZ error, but still have the data mismatch error.

I'll try your suggested process of elimination.'

Thank you

[anasttin]

Hi Micron.

Thanks to you, I found the problem!!

I had a column like this:
Last_Saved_By: Replace(Replace(Replace([Column R],"No",""),"Zero",""),"Yes","")

That Excel could not handle for some reason.

I replaced it with this:
Last_Saved_By: Replace(Replace(Replace(IIf(IsNull([Column R]),"",[Column R]),"No",""),"Zero",""),"Yes","")

To handle null values and it now works perfectly.

Thank you again and have a great day.

[Micron]

Looks to me that the 'some reason' was due to nulls. The first expression tries to coerce 3 values to an empty string, but as previously stated, you can't perform functions on null values. Your solution nicely handles null. Glad I was able to help.

[ArviLaanemets]

So you did copy the sql-string from Access to Excel, and queried from there?

Had you simply created a saved query in Access, and in Excel created a query SELECT * FROM YourSavedQuery, you'd avoided this problem at all.

[June7]

I learned the hard way Excel will not recognize Nz() function. It would not link to query nor even allow VBA to open recordset pulling from query using Nz(). It would not work with query even if it did not use Nz() but any intermediate query objects did.