CODEWITHSUNDEEP
sqlreplacems-access-2013
Matt Myers
Matt Myers

Reputation: 15

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

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

Upvotes: 1

Views: 1851

Answers (1)

kismert
kismert

Reputation: 1692

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.

Upvotes: 4

Related Questions