Access 2021 : "like" clause with SQL don't work with accent

Copper Contributor

Hello, I'm using MS Access 2021 with an Access version 2021 database. I noticed that if I use the "like" clause with a text field containing accented text AND if the field is indexed (with or without duplicates), my SQL query returns no data. The only solution I found so far, is to remove the indexing property on my field. Does anyone have another solution please? Thanks in advance.

3 Replies
Perhaps some examples might clarify the problem description.

Hi Georges, thank you for your help.

The test below explains my problem.

 

I have a table named "Couleurs" (colors in English) used to describe the color of wines ("Vins" in French). The "Vins" table contains the wines with their color.
My goal is to display in a combo-box the wines corresponding to the color I selected in my list-box. But, if the item in my list-box is "----", I want to display all the wines.

 

Now the problem: If the column "Vin couleur" from my table "Vins" is indexed, the query returns no data containing an accent (like "Rosé"). But if I remove the indexation of that column, the SQL works perfectly. Is it possible that the indexation doesn't work if accented data is used?

Thank you and best regards, Jean-Claude (Geneva/Switzerland)

 

Query used in my list-box nammed "zlst" to select which color ("Couleurs") of wine ("Vins") I want to display.
SELECT Couleurs.Field1 FROM Couleurs ORDER BY Couleurs.Field1;

 

Query used in my combo-box to display wines ("Vins") corresponding to the color selected in "zlst"
If the selected item is "----", all items are displayed.

SELECT Vins.Id, Vins.Vin, Vins.Coul FROM Vins
WHERE (((Vins.Coul) Like (IIf([forms]![Form1]![zlst]="----","*",[forms]![Form1]![zlst]))))
ORDER BY Vins.Id, Vins.Vin;

 

Table "Vins" (wines) containing description of wines.

Id            Vin           Vin couleur

1             ----           ----

2             Eta           Rouge

3             Gamma   Rosé

4             Delta       Rosé

5             Thêta      Rouge

6             Epsilon   Rosé-Clair

7             Beta        Clair-Rosé

8             Alpha      Blanc

9             Omega   Rosé-Foncé

@Jesse9263 

There are two possibilities.

 

First, it might be reflecting language collation issues since you are using French words. It might reflect a problem with the language collation used in your instance of SQL Server.

 

The other might be a reflection of the way accented characters are represented in the Access instance.

 

GeorgeHepworth_0-1665403728182.png

 

 

It may reflect a similar issue in the language definition used, or be a result of unicode. Here's an extended discussion of a similar problem.

 

The query is running in Access, so I do think the latter factor is probably more relevant to your problem.

You could try Tom vS's suggestion in the linked thread to use ChrW()  or AscW  to ensure that your comparisons are comparing characters to characters regardless of language. That, of course, is a considerable amount more complex, but it might be the most effective path.