Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Active Server Pages (ASP)
Expertise: Beginner
Feb 4, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Using Numbers in SQL Text


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.

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date