Jet engine seems to have a problem with accented field names referred in user functions

[RagJose]

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.

[ranman256]

string fields must have a quote around them. Usu you can use single quote but accents will mess this up.
so you must use dbl-quotes in order to capture single quotes.

MyField) like """"*"&TypedString&"*"""";

[RagJose]

Thanks, ranman, but it was not a syntax mistake about double or single quotes. The problem was cured just by my replacing an accented character (ó) of my field name with a non-accented one (o).

Quoting actual examples I tried in SQL mode:

select * from MyTable where StripAccents(História) like "*whatever*"; gives an error (StripAccents being my working user function)
select * from MyTable where StripAccents(Historia) like "*whatever*"; works fine (just by using a renamed non-accented field name)
select * from MyTable where left(História) like "*whatever*"; also works ("left" being an intrinsic Access function).

[RagJose]

Any other ideas? Perhaps more likely from natives of tongues with accented words (Spanish, German, French, Hungarian, Portuguese, Italian...)?

[Micron]

if I replace "StripAccents" by an intrinsic Access function like "left", no error is detected

then the problem is in the function. Post that??

[RagJose]

then the problem is in the function. Post that??

Ok, here it goes... it's rather straighforward. I can't see how the external name of the argument could affect anything. Thanks for any insight.

Public Function StripAccents(Texto As String) As String
Dim ComAc As String, SemAc As String, Car As String
Dim iPos As Integer, jPos As Integer

ComAc = "àâêôûãõáéíóúçüÀÂÊÔÛÃÕÁÉÍÓÚÇÜ" 'accented characters
SemAc = "aaeouaoaeioucuAAEOUAOAEIOUCU" 'non-accented equivalents

StripAccents = ""
For iPos = 1 To Len(Texto) ' parse input string
Car = Mid(Texto, iPos, 1) ' char by char
jPos = InStr(ComAc, Car) ' check if accented
If jPos > 0 Then 'if yes (found in ComAc),
Car = Mid(SemAc, jPos, 1) ' replace it by the non-accented version
End If
StripAccents = StripAccents + Car ' build the output string
Next
End Function

[Micron]

OK, more reflection on the posts here and have played with your function. I even created a table with one field named História and a query ran OK (thought I was going to have to alias the field but didn't). It only contains "one" to "three". These sql versions worked OK:

SELECT tblAccented.História FROM tblAccented
WHERE (((tblAccented.História) Like "*on*"));

SELECT * FROM tblAccented WHERE 
(((StripAccents([História])) Like "*on*"));

SELECT * FROM tblAccented WHERE 
StripAccents([História]) Like "*on*";

So no idea now. Also using 2016 thus that doesn't seem to be it because you are too.
If you want to upload a zipped sample db that exhibits the issue, I can try it. If it doesn't present a problem for me but does for you, I'd start comparing regional settings in Windows I guess.

[RagJose]

OK, more reflection on the posts here and have played with your function. I even created a table with one field named História and a query ran OK (thought I was going to have to alias the field but didn't). It only contains "one" to "three". These sql versions worked OK:

SELECT tblAccented.História FROM tblAccented
WHERE (((tblAccented.História) Like "*on*"));

SELECT * FROM tblAccented WHERE 
(((StripAccents([História])) Like "*on*"));

SELECT * FROM tblAccented WHERE 
StripAccents([História]) Like "*on*";

So no idea now. Also using 2016 thus that doesn't seem to be it because you are too.
If you want to upload a zipped sample db that exhibits the issue, I can try it. If it doesn't present a problem for me but does for you, I'd start comparing regional settings in Windows I guess.

Thanks for taking the trouble, Micron. Truly appreciated. However, in the meantime I ran across another DB of mine where the very same function works ok with an accented fieldname argument. So, it's not a regional setting either. BTW, I also tried saving my .mdb as .accdb, and it didn't help. Guess I'll have to keep digging. Will post when/if I find the problem (or a workaround),

[Micron]

Hate to resort to the oft made suggestion because it's like saying "unplug it, wait 10 seconds and plug it back in" as the standard fix when all else fails but did you try a compact and repair? There is no difference between db's where it works and doesn't - like Option Compare Binary in one and Option Compare Database in the other?
Do post your solution if you arrive at one. It might help someone else one day.

[RagJose]

Hate to resort to the oft made suggestion because it's like saying "unplug it, wait 10 seconds and plug it back in" as the standard fix when all else fails but did you try a compact and repair? There is no difference between db's where it works and doesn't - like Option Compare Binary in one and Option Compare Database in the other?
Do post your solution if you arrive at one. It might help someone else one day.

Thanks again, Micron. Yes, did compact and repair, and the compare options are the same in both DBs. I noticed my function was declared Public in the problem DB and not at the working DB. Taking out the Public didn't make it work. I'll keep you posted. Cheers!

[Micron]

Are both of these functions in the same type of module, or do you have one in a standard module and the other behind a form? If so, I'd wager that the one that doesn't work is behind a form. Not declaring a procedure as Public in a standard module should make no difference. Declaring it as Private in a standard module could be an issue, as could declaring it Public in a form module and trying to call it from elsewhere. Maybe clear up what's Public where?

[RagJose]

Are both of these functions in the same type of module, or do you have one in a standard module and the other behind a form? If so, I'd wager that the one that doesn't work is behind a form. Not declaring a procedure as Public in a standard module should make no difference. Declaring it as Private in a standard module could be an issue, as could declaring it Public in a form module and trying to call it from elsewhere. Maybe clear up what's Public where?

Micron, thanks again for keeping the interest. Yes, in both DBs my function is within a standard module. I still can't explain the difference in behavior between them, but I FOUND A WORKAROUND!

The error message was "type mismatch", so I rather blindly tried to force the right type of argument by coding StripAccents(CStr(História)). To my surprise, the error message changed to "Invalid use of Null"!

I concluded that for some mysterious reason my argument was being passed as Null to my function. My next blind shot was to try rescuing it from "nullity" by coding StripAccents(Nz(História)).

AND THAT CURED THE PROBLEM!!

This in fact makes little sense, as the Nz function should just return a zero-length string from a Null argument. But I'm back in business, and won't worry further. Hope this helps someone with a similar problem. Cheers!

[Micron]

Maybe one db table allows and somehow stores zls and in the other db it doesn't. Why else would it work in one db and not the other if everything else checks out?
Glad you found a work around, but I think a purist wouldn't say the mystery was solved, even if the problem is.

[RagJose]

Maybe one db table allows and somehow stores zls and in the other db it doesn't. Why else would it work in one db and not the other if everything else checks out?
Glad you found a work around, but I think a purist wouldn't say the mystery was solved, even if the problem is.

Yes, Micron, I agree. The mystery (or rather the bug) remains. As I wrote in the beginning of this thread, the problem goes away in the "faulty" db, if I just rename the field to a non-accented name. There are not actual zls's anywhere in the picture, and the Nz function, given an argument suspected of nullity, unexpectedly delivered a non-zls result... It's not the first bug I came across with Access VBA, which required a workaround. Sometimes a bug disappeared with the next Access version, without explanation. Thanks again for your interest.

[Micron]

Guess we'll never know.
You're welcome and good luck with your project.