In this article, we will talk about the Data Type Mismatch errors in MS Access.

The “Type mismatch in expression” error indicates that Access cannot match an input value to the data type it expects for the value. For example, if you give Access a text string when it is expecting a number, you receive a data type mismatch error.

Let us look at some situations where this error can occur.

Example 1: You are trying to join two tables on a field, but the data types of the joined fields are not the same.

Imagine you are trying to join the customer and supplier tables below on Postcode. But the postcode fields have different data types in the two tables as seen below:

So, if you try to join using the condition

WHERE customer.postcode = supplier.postcode

You will get the data type mismatch error. In this case, you need to verify that the data type of each pair of joined fields in the query is the same. If not, either

1. Change the data type of one of the joined fields to match the data type of the other so you don’t get the mismatch error OR
2. Use conversion function to convert the data type

WHERE customer.postcode = CStr(supplier.postcode)

Example 2: 

Similar to above example, you need to verify that the criteria for a column matches the data type of the column’s underlying field in the WHERE clause of an SQL query. If you specify text criteria for say a numeric or date/time field,

WHERE customer.age > “60”

or

WHERE customer.dateofbirth < “01/01/1977”

you’ll get this error:

An error message is returned because Access interprets values in quote marks as text, not numbers. So, the valid syntax will be:

WHERE customer.age > 60

(Numbers without quotes for numeric data)

and

WHERE customer.dateofbirth < #01/01/1977#

(Dates should be entered within two hashes – #<Date>#)

Example 3: The expression for default value of a field does not evaluate to the same data type as the field.

In the above example, the field is of numeric type and we are trying to assign it a text value. When you try to save, you will get the below error.

As mentioned above, any data within double quotes (“”) is treated as a string. So, you will get the error even if you try to enter a number with double quotes – “4086” as the default value. A valid value will be only – 4086 – without the quotes

Note: To change the default value of a field, you need to go to the table’s design mode.

Example 4: You typed the dollar sign ($) in criteria you specified for a Currency field.

While building a query through design mode, if you enter a condition for a currency field as, say, >$50, Access automatically encloses the string you type in quote marks. Thus, it will be interpreted as a String instead of currency and an error will be thrown. You need to remove the dollar sign, and then view the results.

Other situations where this error can occur:

You’re specifying criteria for a Lookup field, and the criteria use the values that are shown (like a name) in the lookup list instead of their associated foreign key values (like an ID). Because the foreign key values are the values actually stored in the underlying table, you should use them when you specify criteria for a field.