Data type mismatch error in criteria expression in ms access 2007

Question

I am trying to run an update query but it is giving me "Data type mismatch in criteria expression". It was working just fine before but now, all of sudden it started giving me this error. I tried doing research and also checked in stack overflow but could not find the solution. I only have one criteria expression in my query.

UPDATE dbo_tblGoods INNER JOIN qryValidate
   ON dbo_tblGoods.MaterialID = qryValidate.MaterialID
SET dbo_tblGoods.BarcodeType = [qryValidate]![BarCodeType],
    dbo_tblGoods.BarCode = [qryValidate]![BarCode]
WHERE (((Right$([NewBarCode],4))="GOOD"));

Also, qryValidate is:

SELECT Parts.MaterialID, Validate_UPC([Parts]![Barcode],[Parts]![BarcodeType]) AS NewBarCode,
Parts.BarCodeType, Parts.BarCode
FROM dbo_tblgoods INNER JOIN Parts 
  ON dbo_tblgoods.MaterialID = Parts.MaterialID;

winghei winghei · Accepted Answer · 2016-08-16T20:03:36

Whenever I got the error "Data type mismatch, I always check those fields I have with expression "=,>.." or Function. Most likely, the cause of the error is a Null field.

Try to check your function Validate_UPC([Parts]![Barcode],[Parts]![BarcodeType]) and change it to Validate_UPC(nz([Parts]![Barcode]),nz([Parts]![BarcodeType])) for Null fields.

If it doesn't work, then go check if the parameters in Validate_UPC matches the data type you supplied in your query. e.g. If you declared Barcode as Integer, a string field is not valid. Try to declare the Barcode or BarcodeType as Variant and properly handle them in VBA.

Lastly, you are trying to inner join fields on two or more tables that are not of the same data field types. I often make this mistake when I create new temporary tables out of data imports(e.g. excel imports)

Data type mismatch error in criteria expression in ms access 2007

2 Answers