Data Type Mismatch in Criteria Expression Fix 

Data type mismatch in criteria expression happens when the criteria you type into a query doesn’t match the data type of the field you’re filtering  for example, typing text criteria for a Number field, or forgetting the # symbols around a date. Jump to the cause that matches your situation: text vs. number · dates · currency · lookup fields · joined fields · VBA/Error 3615. 

What Does “Data Type Mismatch in Criteria Expression” Mean?

Data type mismatch in criteria expression is an error Access throws when it can’t match a value you’ve entered against the data type it’s expecting for that field. If a field is set to Number and you type text into its criteria, Access can’t reconcile the two  and the query stops instead of guessing.

Why Am I Getting This Error? (The 5 Most Common Causes)

Text criteria on a Number or Date/Time field {#text-number}

This is the single most common trigger. Access treats anything wrapped in quote marks as text, even if it looks like a number.

What you typed	Access sees it as	Fix
“50”	Text	Type 50 (no quotes) for a Number field
“12/13/12”	Text	Use #12/13/12# for a Date/Time field
Widget (no quotes, text field)	Access adds quotes automatically	Fine  leave as-is

You can tell if numeric criteria isn’t being read as a number if it shows up with quote marks around it. That’s your visual tell that something’s off before you even run the query. 

Missing delimiters quotes for text, # for dates {#dates}

Every data type in Access has its own “wrapper”:

• Text → double quotes: “Chicago”
• Date/Time → pound signs: #12/13/2026#
• Number/Currency → no wrapper at all

If you type a valid date into the Criteria row, Access will auto-wrap it in # signs for you  that’s confirmation it’s reading it correctly. If it doesn’t, the field likely isn’t formatted as Date/Time, or your date format is ambiguous (Access strongly prefers US-style MM/DD/YYYY or ISO YYYY-MM-DD to avoid confusion).

Dollar signs in Currency field criteria {#currency}

Typing a dollar sign in criteria for a Currency field causes this error, because Access automatically wraps whatever you type in quote marks once it sees the $ sign  turning your number into text. Fix: type 50 instead of $50. Access will still display it as currency in the results. 

Lookup fields vs. foreign key values {#lookup}

This one trips up even experienced users. If a field uses the Lookup Wizard, Access shows you a friendly value (like a city name) in the Datasheet view  but the table actually stores a numeric ID behind the scenes.

When you specify criteria for a Lookup field using the displayed value instead of its underlying foreign key value, you’ll get a data type mismatch  because the foreign key is what’s actually stored in the table, and that’s what your criteria needs to match. 

Example: Your Addresses table shows “London” in the City column, but internally stores CityID = 4. If City is a Number field wired to a Lookup, typing City = “London” fails. You’d either use CityID = 4 directly, or join Addresses to the Cities table and filter on the actual text City field there.

Mismatched data types on joined/linked fields {#joins}

If you’re joining two tables in a query, both sides of the join need the same data type. Verify that the data type of each pair of joined fields in the query is the same, and if not, change one to match the other, a common trap when one table stores an ID as Number and a linked or imported table stores the same ID as Text. 

Is This Error 3615? (VBA & Code-Side Causes)

If you’re seeing this inside VBA rather than the query grid, you’re likely looking at Runtime Error 3615, the code-level version of the same problem. It shows up when:

• A WHERE clause or SQL string built in VBA compares mismatched types (often because a variable holding a number got concatenated as a string, or vice versa)
• Data is being imported from Excel with the wrong data type assigned during import
• Two linked tables have incompatible join field types
• Rarely, the underlying database file itself is damaged

Fix: open the VBA Editor (Database Tools → Visual Basic), find the object throwing the error, and check every expression that builds a criteria string make sure numbers aren’t being wrapped in quotes and dates are wrapped in #.

Worked Example  Fixing a Real Broken Query

Say you’re filtering an Orders table for orders placed on December 13, 2026, with an OrderTotal over $50, using a linked Customer lookup for CustomerCity.

Broken criteria:

• OrderDate: “12/13/2026”
• OrderTotal: >”$50″
• CustomerCity: “Chicago” (but CustomerCity is a Lookup field storing a numeric CityID)

Fixed criteria:

• OrderDate: #12/13/2026#
• OrderTotal: >50
• CustomerCity: join to the Cities table and filter City = “Chicago” on the actual text field, or use the correct CityID number directly

Three separate mismatches, three separate fixes and this is exactly why the error message doesn’t tell you which field is the problem. You have to check each criteria field against its actual data type in the Design view.

When It’s Not Your Query  Database Corruption

If none of the above applies and the error appears unpredictably (working one day, breaking the next, with no criteria changes), the .accdb or .mdb file itself may be damaged. Run Compact and Repair (Database Tools → Compact and Repair Database) before spending more time on the query logic  it resolves a surprising number of “phantom” mismatch errors.

How to Prevent This Error Going Forward

• Check each field’s data type in Design view before writing criteria
• Never wrap numbers or currency in quotes  no $, no “”
• Always let Access auto-add # around dates; if it doesn’t, your format or field type is wrong
• For Lookup fields, filter on the real underlying field (join to the source table) rather than the displayed value
• Keep joined fields on the same data type across every linked or imported table
• Compact and repair your database periodically, especially after large imports

Conclusion

The “Data Type Mismatch in Criteria Expression” error in Microsoft Access usually occurs when a query compares incompatible data types, such as text with numbers or dates with strings. By checking your field data types, using the correct criteria syntax, and validating query parameters, you can quickly resolve the error and prevent it from happening again. Following proper database design and testing practices will help keep your Access database accurate, reliable, and error-free.

FAQs