How to Fix "Type Mismatch in Expression" Error in MS Access?

The Access error 3615 – Type Mismatch in the Expression error indicates that the application fails to match an input value to the expected data type value. The error can appear when you create a table relationship in Access or use a join to the queries. It usually occurs if there are incompatible data types. You can get this error in the following scenarios:

• When using built-in functions, such as Cstr(), cDate(), Cint(), etc. in VBA queries.
• When creating queries in VBA code to perform calculations or to compare fields of two database files in the VBA project.  
• Importing data from external sources (such as Excel).
• Exporting data from the Access application to other sources.
• When defining the relationship between tables in the Access database.
• When trying to perform operations in a damaged or corrupted Database file. 

What Causes the “Type Mismatch in Expression” Error in MS Access?

This error can occur if you try to establish a relationship in two tables with mismatched datatypes. There are many other reasons that may lead to this error, such as:

• Incorrect Date and Time formats
• Invalid syntax of inbuilt functions in your queries or expressions in VBA
• Incorrect format of data conversion functions
• Issue with the SQL queries (Join usage)
• Issues with datatype formatting when importing or exporting data in Access
• Data you are entering in the database file mismatches with the defined validation rules
• Objects in database files are corrupted
• Database is damaged or corrupted 

Methods to Fix “Type Mismatch in Expression” Error in MS Access

In Access, data type mismatch in criteria expression error can occur if there is a dollar sign ($) in the specified criteria for a currency field in the database. You can try removing the dollar sign to fix the issue. If the problem is not resolved, then follow the below methods:

Method 1: Check for Incompatible Datatype in the Fields 

The error 3615 - Access datatype mismatch in criteria expression can occur when attempting to establish a relationship between two tables using incompatible datatype in the fields. Ensure you have compatible datatypes in the tables of the database files. For example, if you have two tables in the Access database: “Customer” and “Order.” The field ID in the “customer” table and Order ID in the “Orders” table are set as Autonumber datatype. When trying to establish a relationship between two tables, the Access application reads these autonumber datatypes as distinct datatypes. When the Autonumber datatype is set in the table field, it automatically generates a unique, sequential number for its table, leading to a mismatch. You can use a common data type, such as a number, instead of the Autonumber.

Here are the steps to change the datatype in Access:

• Open the database.
• Go to the Navigation Pane and double-click on the table you want to modify.
• Select the Design View
  

  

  
  
• Click on the field you need to change, click on the dropdown, and select a new Datatype. 
• Save the applied changes.

Method 2: Check the Table Relationships 

You can also check the table relationships. The table relationships window can help you identify the issues related to data type discrepancies between linked fields and relationships. To open the Relationships window, follow the below steps:

• Navigate to File and then click
• Select the desired database.
• Under the Database Tools tab, click on Relationships.
  

  

  
  
• Click the Design tab and then click All Relationships.
  

  

  
  

• The Relationship window displays all the defined relationships in your database.
• Check and fix if there are any relationship issues.  

Method 3: Check and Fix the Queries

The “Type mismatch in expression” error can also occur when running queries (SQL-specific query, parameter query, action query, or crosstab query) in the database. It usually appears when you use incorrect syntaxes or if the joining fields contain wrong data types. Make sure you’re using the same data types in the fields. You can check the joined fields in your queries to troubleshoot the issue.

Method 4: Check the VBA Code

The “Datatype mismatch in expression” error in Access can occur due to incorrect variable and method declaration and mismatched datatypes in the variables in the VBA code. You can review the VBA code and try to fix the typos and other syntax issues. Here is how to do so:

• Open the desired Access database.
• Go to the Database Tools option and then select Visual Basic.
• In VBA Editor, in the navigation pane, you can find the project and its module, objects, form names, etc.
• You can click on the related object to view the corresponding code.
• Verify all the syntaxes and queries in the code and do the changes if required.

Method 5: Repair Access Database

You can get the “Datatype mismatch in criteria expression” error if the datatype discrepancies occurred due to corruption in the Access database file. If part of your MDB or ACCDB file gets damaged, you can repair it using the ‘Compact and Repair’ utility in MS Access. Here’s how to use this utility:

• Open Microsoft Access.
• On the Templates window, double-click the Blank Database option.
• Click File and then click Close. 
• Go to Database Tools and then click Compact and Repair Database
  
• On the ‘Database to Compact from’ window, browse and select the database you want to compact and repair. Click Compact.
• Select a location to save the repaired database file and click Save.
• Wait for the process to complete and then check if the error is fixed.

In case the Compact and Repair utility fails to repair the corrupted Access database, then the best option is to use a professional Access database repair tool, such as Stellar Repair for Access. The tool can fix highly corrupted Access database (.mdb or .accdb) file and recover all the objects while preserving the data integrity. The tool is compatible with all the MS Access versions, including MS Access 2019.

Closure

The datatype mismatch in criteria expression error can occur due to issues in the datatype associated with the fields and queries. You can follow the methods discussed in the article to troubleshoot the problem. If corruption in the database file causes this error, then try using Stellar Repair for Access. This tool can repair both .mdb and .accdb files and restore all the data in a new database file, keeping the original formatting.