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.

Share the Post:
data observability

Data Observability Explained

June 8, 2023

Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the

Heading photo, Metadata.

What is Metadata?

June 1, 2023

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

Show More