Type mismatch in expression access ошибка

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.

Hi Guys

I created query using a table and a query.

it had worked before, but suddenly error pop-ed up which is «Data Type Mismatch in Criteria Expression».

I searched on google and I added other condition «is not null» or use Like function, but it still doesn’t work.

Can you please check my query?

SELECT [Qry_EW Raw Data].[GSFS Receipt No*], [Qry_EW Raw Data].[Auth Claim No], [Qry_EW Raw Data].[Company Name], [Qry_EW Raw Data].[System inv flag], [Qry_EW Raw Data].[Repair End Date-YYYYMMDD], [Qry_EW Raw Data].[Technical Remark], [Qry_EW Raw Data].[End User Name], [Qry_EW Raw Data].[Postal Code], [Qry_EW Raw Data].[Phone Number], [Qry_EW Raw Data].[State Name] AS State, [Qry_EW Raw Data].[Division Name], [Qry_EW Raw Data].[Model Code], [Qry_EW Raw Data].[Serial No], [Qry_EW Raw Data].TypeCode, [Qry_EW Raw Data].[Primary Repair Code], [Qry_EW Raw Data].[Warranty Reject Reason (Multi)], [Qry_EW Raw Data].[Receipt Remark] AS [Explanation Content], [Qry_EW Raw Data].[ASC Repair Remark], [Qry_EW Raw Data].[Approval Visit Txn Amount] AS Visit, Price_List_Comerco.Price, [Price]+[Visit] AS Pricettl, IIf([State]="AB" Or [State]="QC" Or [State]="BC" Or [State]="SK" Or [State]="MB",0.05,IIf([State]="NS" Or [State]="NB",0.15,IIf([State]="ON",0.13,IIf([State]="PE" Or [State]="NL",0.15,100)))) AS [GST/HST Rate], IIf([Company Code]="HA",IIf([State]="QC",0.09975,IIf([State]="SK",0,IIf([State]="MB",0.08,0))),IIf([State]="QC",0.09975,IIf([State]="BC",0.07,IIf([State]="SK",0,IIf([State]="MB",0.08,0))))) AS [PST Rate], [Pricettl]*[GST/HST Rate] AS [GST/HST], IIf([State]="SK" Or [State]="MB" Or [State]="BC",[Pricettl]*[PST Rate],[Pricettl]*[PST Rate]) AS PST, [Pricettl]+[GST/HST]+[PST] AS Total, (IsNull([Parts Desc1])+1)+(IsNull([Parts Desc2])+1)+(IsNull([Parts Desc3])+1)+(IsNull([Parts Desc4])+1)+(IsNull([Parts Desc5])+1)+(IsNull([Parts Desc6])+1)+(IsNull([Parts Desc7])+1)+(IsNull([Parts Desc8])+1)+(IsNull([Parts Desc9])+1)+(IsNull([Parts Desc10])+1) AS PartsUsed, [Qry_EW Raw Data].[Parts Desc1], [Qry_EW Raw Data].[Parts Desc2], [Qry_EW Raw Data].[Parts Desc3], [Qry_EW Raw Data].[Parts Desc4], [Qry_EW Raw Data].[Parts Desc5], [Qry_EW Raw Data].[Parts Desc6], [Qry_EW Raw Data].[Parts Desc7], [Qry_EW Raw Data].[Parts Desc8], [Qry_EW Raw Data].[Parts Desc9], [Qry_EW Raw Data].[Parts Desc10]
FROM [Qry_EW Raw Data] LEFT JOIN Price_List_Comerco ON [Qry_EW Raw Data].TypeCode = Price_List_Comerco.Type
WHERE ((([Qry_EW Raw Data].[Company Name])="xyz"));