Using Arabic text in the where clause of a query – Access

[Sulyim]

Hi,
I am using MS Access 2013 as a front end to retrieve data from a database (SQL Server 2008 R2) via (ODBC), and the tables are linked.

I created a query to retrieve all the records in the table (categories) filter records based on an Arabic text in the field (name), for example:

select * from categories where name like '*خدمة *';



This query returns nothing when the table is linked to the SQL server, but when I converted the same table to local table, this query works and returns all the records with that condition.

I appreciate if you can help me to solve this problem.

Sulyim

[CJ_London]

not quite sure about retrieving data via linked tables to sql server, but if your query was in sql server you use % as the wildcard, not * - and if I recall correctly alike rather than like.

So suggest try that first.

You can also try changing the db to work on ANSI92 and use Alike and % (File>Options>Object Designers>sql server compatible syntax.


The other thing is when linking you may have been asked to specify the primary key field, failure to do so means queries will not work as expected.

Note also that using an initial % (or *) means that the query engine is unable to use indexing so filtering will be based on a sequential search which is considerably slower than a filtered search - depending on the number of records, could be 10-100 times slower. So use the initial wildcard with care.

[Sulyim]

Dear Ajax,

I have changed the db to work on ANSI92 as you mentioned, unfortunately, it did not work.

The problem is not with the wildcard (* or %). The problem with Arabic letters because when I use English letters in the query it works, for example:

select * from categories where name like '*something*';

[CJ_London]

Regret this is something outside my experience.

If it is something to do with the characterset, I would find something that is in the sql database and check what the ascii codes are for each character and confirm they are the same in your search string.

It may be that you need to use the instr function and set the binary comparison

select * from categories where name instr([name],'something',1)<>0

https://support.office.com/en-us/art...8-77cd0cb8a55b

or perhaps the instrB funtion