Frech, Spanish, Deuch accents on a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Warrio - 04 Oct 2006 11:32 GMT

Hello!

How is possible to type for example "à uinetas" and find a record stored in
the database as "á uiñetas"?

I'm looking to remove all the accents (or at least the most used ones) from
each side of the WHERE clause.
I tried to use the following query, but unfortunately, I get and error msg
saying "Expression too complex in query expression". I guess that I cannot
use more than 9 or 10 embedded functions..

SELECT * FROM Table1 WHERE

Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(

Table1.Field1

,' ','*'),'De ',''),'Del
',''),'-',''),'_',''),'.',''),',',''),'/',''),'\',''),'(',''),')',''),"'",""),'à','a'),'ä','a'),'â','a'),'ã','a'),'á','a'),'é','e'),'è','e'),'ë','e'),'ê','e'),'ç','c'),'ï','i'),'î','i'),'ì','i'),'í','i'),'ô','o'),'õ','o'),'ò','o'),'ó','o'),'û','u'),'ù','u'),'ú','u'),'ü','u'),'ñ','n')

LIKE

Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
"*à uinetas*"
,' ','*'),'De ',''),'Del
',''),'-',''),'_',''),'.',''),',',''),'/',''),'\',''),'(',''),')',''),"'",""),'à','a'),'ä','a'),'â','a'),'ã','a'),'á','a'),'é','e'),'è','e'),'ë','e'),'ê','e'),'ç','c'),'ï','i'),'î','i'),'ì','i'),'í','i'),'ô','o'),'õ','o'),'ò','o'),'ó','o'),'û','u'),'ù','u'),'ú','u'),'ü','u'),'ñ','n')

Thanks for any suggestion.

Reply to this Message

Neil Sunderland - 04 Oct 2006 12:01 GMT

>How is possible to type for example "à uinetas" and find a record stored in
>the database as "á uiñetas"?
[quoted text clipped - 4 lines]
>saying "Expression too complex in query expression". I guess that I cannot
>use more than 9 or 10 embedded functions..

One way is to use a VBA function to do the replacing. Paste this into
a new module called modReplaceAccents:

Public Function ReplaceAccents(ByVal sData As Variant) As String
ReplaceAccents = "" & sData 'To handle any NULLs
ReplaceAccents = Replace(ReplaceAccents, "à","a")
ReplaceAccents = Replace(ReplaceAccents, "ä","a")
'Repeat for as many characters as you need to replace!
End Function

Then change your query to:

SELECT *
FROM Table1
WHERE ReplaceAccents([Table1].[Field1])
LIKE ReplaceAccents('*à uinetas*')

Signature

Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

Reply to this Message

Warrio - 04 Oct 2006 12:18 GMT

Sure I can do that, but I'm using this in a search form, so I need to get my
result as quickly as possible..
but when I use a function written in VBA mixed to a query, I'm afraid it
will make it slower if your query will be called on each of the 1000...
records stored in Table1

and what about the ASC function? isn't there a way to do something with the
character's code?

Thanks again

>>How is possible to type for example "à uinetas" and find a record stored
>>in
[quoted text clipped - 23 lines]
> WHERE ReplaceAccents([Table1].[Field1])
> LIKE ReplaceAccents('*à uinetas*')

Reply to this Message

Neil Sunderland - 04 Oct 2006 12:53 GMT

>>>How is possible to type for example "à uinetas" and find a record stored
>>>in the database as "á uiñetas"?

>> One way is to use a VBA function to do the replacing. Paste this into
>> a new module called modReplaceAccents:
[quoted text clipped - 11 lines]
>> WHERE ReplaceAccents([Table1].[Field1])
>> LIKE ReplaceAccents('*à uinetas*')

>Sure I can do that, but I'm using this in a search form, so I need to get my
>result as quickly as possible..
>but when I use a function written in VBA mixed to a query, I'm afraid it
>will make it slower if your query will be called on each of the 1000...
>records stored in Table1

1000 rows? You must have a really sloooow computer :)

>and what about the ASC function? isn't there a way to do something with the
>character's code?

That's still going to impact on performance; if that's really an issue
perhaps you should have an additional field in your table which stores
the value without the accents, and search on that?

Signature

Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

Reply to this Message

Warrio - 04 Oct 2006 14:08 GMT

I've put ... after 1000, that means today the table has about 3000 records.
but it's made so tomorrow, it contains much more
plus it's on a network 10M, so I'm always looking for a better performance.
Thanks any way for your help!!

>>>>How is possible to type for example "à uinetas" and find a record stored
>>>>in the database as "á uiñetas"?
[quoted text clipped - 31 lines]
> perhaps you should have an additional field in your table which stores
> the value without the accents, and search on that?

Reply to this Message