Frech, Spanish, Deuch accents on a query

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(Re place(Replace(Replace(Replace(Replace(Replace(Repl ace(Replace(Replace(Replace(Replace(Replace(Replac e(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(Re place(Replace(Replace(Replace(Replace(Replace(Repl ace(Replace(Replace(Replace(Replace(Replace(Replac e(Replace(Replace(Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace(Replace(Replace(Re place(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.

Warrio wrote:
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..

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*')


--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

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


"Neil Sunderland" a écrit dans le message de
news: ...
Warrio wrote:
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..

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*')


--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

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

Neil Sunderland wrote:
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*')

Warrio wrote:
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?

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address

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!!



"Neil Sunderland" a écrit dans le message de
news: ...
Warrio wrote:
How is possible to type for example "à uinetas" and find a record stored
in the database as "á uiñetas"?

Neil Sunderland wrote:
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*')

Warrio wrote:
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?

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address