Microsoft Access error - Data type mismatch in criteria expression

Question

Receive error in Microsoft Access when running a query - data type mismatch in criteria expression. How can I resolve this error?

Answer 1

In a query's WHERE clause:

Values of text data type must be delimited by literal quote characters, e.g. Product = "Widget"

Values of DateTime data type must be delimited by # characters and be either in US date format or an otherwise internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD, e.g. TransactionDate = #2025-07-04#

Values of a number data type do not need delimiters, e.g EmployeeID = 666

If a parameter is used the parameter does not need to be delimited, whatever its data type, e.g. AppointmentDate >= Forms!MyForm!txtApppointmentDate

A common reason for a type mismatch error is where the actual value in a column is a number, but the value seen in the column in datasheet view or in a control in a bound form is looked up in a related table. This will be the case if the 'lookup field' wizard has been used when the column was created in table design view. For example, in a table of Addresses or similar, you might have a column containing a CityID numeric value, but you'll see a value of text data type such as London. In this case you can either use the numeric CityID value (without delimiters), which will be the primary key of the referenced Cities table, or join the Addresses and Cities table in a query, in which case the criterion City = "London" can be on the text City column or similar in the Cities table.

Answer 2

Can you post some screenshots showing the error message and the design view of your query? Also, consider posting the SQL statement for your query.