I get a message about data type mismatch

Applies to: Access for Office 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

This error highlights that Access is unable to 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 altered the Default Value for a field in a table and notice a message about data type mismatch once you attempt to type new records, launch the table in Design view and check 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.)
  • Confirm that the criteria for a column reflects the data type of the column’s underlying field. If you specify text criteria for a numeric or date/time field, you’ll receive this error. As an example, if you enter the criteria “50” or “12/13/12”, an error message is relayed because Access interprets values in quote marks as text, not numbers. Thus, always erase those quote marks. You’ll know once you type a valid date in the Criteria field because # signs will emerge around the date, like #12/13/12#.

Other situations that lead to a data type conflict include:

  • You’re specifying criteria for a Lookup field, and the criteria use the values that are displayed (like a name) in the lookup list rather than their related foreign key values (like an ID). Because the foreign key values are the values truly stored in the original table, you should apply them anytime you specify criteria for a field.
  • You stated the dollar sign ($) in criteria you specified for a Currency field. Delete the dollar sign, and then review the results. You can decipher if the numeric criteria you typed isn’t a number if it enclosed by quote marks around it. When you type the $ sign, Access promptly wraps the string you type in quote marks.
  • Authenticate that the data type of each pair of joined fields in the query is the same. If not, amend the data type of one of the joined fields to correspond to the data type of the other so you avoid getting the mismatch error.

Leave a Reply

%d bloggers like this: