Here is the code I am using to do a query from an Access 97 database:
Query = "SELECT * FROM DiamondList WHERE (ProductNumber = '" & tempProduct & "')"
ProductNumber is the name of an Access 97 database field with the data type set to number. When I try running the script, I get this error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
Do I get this error because my field is set to a number (long integer) and my tempProduct variable is text? If so, how can I correct this problem?
If you are trying to use a numeric value, do not use the apostrophe (') character around it. Change your SQL statement to:
Query = "SELECT * FROM DiamondList WHERE (ProductNumber = " & tempProduct & ")"
and you should be all set. Because tempProduct is a variant, it can be concatenated with a SQL string with no problem.