Query using only ASCI characters

[Warrio]

Hello!

Is it possible to have a query that transforms the content of a field into
ASCI (SET of the 128 first characters)

The only solution that I see now is to do it with VBA Code with a first loop
on the records and a second on each character of the field

The purpose of that is to avoid replica of data, in case that a user has
inserted once a name with an accent and wants to insert one more time
without, because if I simply compare "Noël" with "Noel", I won't avoid a
replica because these two name are considered as the same by the where
clause 'LIKE' or the operator '='..

Thanks for any advice

 

[Warrio]

Hi,


Jet is case insensitive but accent sensitive. MS SQL Server can have
different setting, such as case sensitive and accent insensitive, if you
want, or other mixture, and MS SQL Server 2005 should be able to change that
kind of setting, "ON THE FLY", but MS SQL Server 2000 requires a
re-installation (or second copy) of the engine (if memory serves, and if no
service patch now allows that change "on the fly"). That change is likely to
require the index on string values to be rebuild. Not something you
necessary want.

So, basically, you may be obliged to use Replace (ACCESS 2000 or later),
to replace the accents in the input, and, for values in the table, add a
column, or a table, of aliases.


Aliases ' table name
This aka ' fields name
Noël Noel
Québec Quebec
Québec Quebec City
Ottawa Bytown
Istanbul Constantinople
Istanbul Byzance
....

and so on. You could then start to see if the name you look for is in the
aka and if so, use the "this" value than the original value, to continue you
research. The advantage of the table (by comparison to the extra field) is
that it allows multiple aliases for a given name.



Hoping it may help,
Vanderghast, Access MVP