I developed a small Access application which includes a search to select records containing a user-typed keyword in a specific field. Lately, I tried to make this search "smarter" by not distinguishing between non-accented and accented characters, which are common in Portuguese. To that purpose, I wrote a small string function that takes out all accents of the argument string. It works fine. However, when trying to use it in the application, I get a "Data type mismatch in criteria expression". Stripped down to the bone, the search query looks like
select * from MyTable where StripAccents(MyField) like "*"&TypedString&"*";
Yes, StripAccents is a string function with a single string argument and MyField is a short text table field. Moreover, if I replace "StripAccents" by an intrinsic Access function like "left", no error is detected and the query runs.
To cut the story short, I just found the problem was that the actual name of the placeholder "MyField" has an accent. Changing that field name to a non-accented string cured the problem. Clearly an Access bug (I have the 2016 version).
Anyone had a similar experience and found a workaround? I don't feel very eager to revise all my objects and codes which use the accented name if I don't have to.