Data type mismatch in criteria expression (Excel table; built in f

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

"Data type mismatch in criteria expression"
I got this error message when using Access's "Built in Functions" to built criteria on a query which is based on an excel table.
The excel table is created by exporting from Outlook.
No error when using table created from within Access itself.
Does anyone has any clue about solving this? Thanks.
David
email: (e-mail address removed)
 
Additional details:
Here is the tail end of my SQL:

WHERE ((ISNULL(InStrRev(([Contacts].[Categories]),"Favorites"))));
 
InStrRev() should return a numeric value. What are you attempting to do with
the IsNull()? Did you copy this from your SQL view? If so, did ISNULL really
display all caps?

--
Duane Hookom
MS Access MVP


david said:
Additional details:
Here is the tail end of my SQL:

WHERE ((ISNULL(InStrRev(([Contacts].[Categories]),"Favorites"))));

david said:
"Data type mismatch in criteria expression"
I got this error message when using Access's "Built in Functions" to built criteria on a query which is based on an excel table.
The excel table is created by exporting from Outlook.
No error when using table created from within Access itself.
Does anyone has any clue about solving this? Thanks.
David
email: (e-mail address removed)
Click to expand...
Click to expand...
 
Huh, I go this solved!
By changing the SQL to as follows:
WHERE (((IsEmpty([Contacts].[Categories]))=False)) OR (((InStrRev(([Contacts].[Categories]),"sin"))<>"0"));

After poking around more, I've discovered that the original problem was caused by the presence of empty cells in the excel table. Adding the IsEmpty test solved it.

Thanks Dusane - The ISNULL was entered by hand, as are the IsEmpty phrase above. I simply brought up the SQL view and started typing.



Duane Hookom said:
InStrRev() should return a numeric value. What are you attempting to do with
the IsNull()? Did you copy this from your SQL view? If so, did ISNULL really
display all caps?

--
Duane Hookom
MS Access MVP


david said:
Additional details:
Here is the tail end of my SQL:

WHERE ((ISNULL(InStrRev(([Contacts].[Categories]),"Favorites"))));

david said:
"Data type mismatch in criteria expression"
I got this error message when using Access's "Built in Functions" to built criteria on a query which is based on an excel table.
The excel table is created by exporting from Outlook.
No error when using table created from within Access itself.
Does anyone has any clue about solving this? Thanks.
David
email: (e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
...
InStrRev() should return a numeric value. What are you attempting to do with
the IsNull()? Did you copy this from your SQL view? If so, did ISNULL really
display all caps?
Click to expand...

I think the OP means they get no error when using the table within the
Access UI but does when running the same query without MS Access e.g.
ADO in Excel VBA code. InStrRev does not appear to be a Jet function.

Jamie.

--
 
Back
Top