Oct 07 2022 07:36 AM
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.
Oct 07 2022 12:47 PM
Oct 09 2022 11:50 PM
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é
Oct 10 2022 05:13 AM
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.
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.