This 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.
To troubleshoot this message, try the following:
-
If you just changed the Default Value for a field in a table and see a message about data type mismatch when you try to enter new records, open the table in Design view and make sure the expression you use for the field's Default Value evaluates as the same data type as the field. For example, if the field has the Number data type, make sure you don't put quote marks around the Default Value expression (e.g., "1" would evaluate as text, not a number.)
-
Verify that the criteria for a column matches the data type of the column's underlying field. If you specify text criteria for a numeric or date/time field, you'll get this error.
As an example, if you type the criteria "50" or "12/13/12", an error message is returned because Access interprets values in quote marks as text, not numbers. So be sure to remove those quote marks. You'll know when you enter a valid date in the Criteria field because # signs will appear around the date, like #12/13/12#.
Other situations that cause a data type conflict include:
-
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.
-
You typed the dollar sign ($) in criteria you specified for a Currency field. Remove the dollar sign, and then view the results.
You can tell if the numeric criteria you entered isn't a number if it has quote marks around it. When you type the $ sign, Access automatically encloses the string you type in quote marks.
-
Verify that the data type of each pair of joined fields in the query is the same. If not, 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.
