Get Data Type Mismatch error when using Replace Function in WHERE clause of MS Access Update statement

Question

I'm getting the error message "Data type mismatch in criteria expression" with the following UPDATE statement executing in MS Access 2013.

UPDATE tblTasks 
   SET tblTasks.IconFilePath = "C:\Images\john.smith.jpg"
 WHERE (((Replace([PersonResponsible]," ",""))="Smith,John") 
   AND ((Len([PersonResponsible]))>0));

When I remove the Replace function call from the WHERE clause the statement executes without error.

The Data Type of the PersonResponsible field is Short Text(255). I have tried wrapping the the Replace call in CStr and also NZ but still got the data type mismatch error.

Any thoughts are appreciated.

Thank you

Can we see the John Smith data line to reproduce issue on our end? — Parfait
HI sorry I'm not following that question. "Smith,John" is quite literally what is being passed in the query. I must be missing something. Can you elaborate on that request for the "John Smith data line"? — Matt Myers

kismert kismert · Accepted Answer · 2016-05-24T17:40:36

If [PersonResponsible] allows nulls, change your SQL to:

UPDATE tblTasks 
   SET tblTasks.IconFilePath = "C:\Images\john.smith.jpg"
 WHERE (((Replace(Nz([PersonResponsible],"")," ",""))="Smith,John") 
   AND ((Len(Nz([PersonResponsible],"")))>0));

But really, you can simplify this to:

UPDATE tblTasks 
   SET tblTasks.IconFilePath = "C:\Images\john.smith.jpg"
 WHERE (((Replace(Nz([PersonResponsible],"")," ",""))="Smith,John"));

The extra where clause doesn't add any more specificity in this case.

Get Data Type Mismatch error when using Replace Function in WHERE clause of MS Access Update statement

1 Answers