Frech, Spanish, Deuch accents on a query

[Warrio]

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.

 

[Neil Sunderland]

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

 

[Warrio]

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

 

[Neil Sunderland]

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

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?

 

[Warrio]

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

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

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