devxlogo

Using Numbers in SQL Text

Using Numbers in SQL Text

Question:

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?

Answer:

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.

See also  lenso.ai - an AI reverse image search platform
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist