UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help Searching Special Characters, Access 2013    
 
   
Pascolo73
post Aug 31 2018, 01:26 PM
Post#1



Posts: 21
Joined: 7-July 18



Good morning all from the Forum.
This is the first time a post a question in UtterAccess. I hope I get it right.

My question is regarding searches for names with special characters. You see, I live in a Spanish speaking country and there are a lot of special characters in my language that are not included in English. (like the word "Español" [Spanish]). So I have this app that includes a client table. When I search in this table using the usual commands and the name I'm searching for contains any one of these character it does not show up in the results of the search. So far I have managed to write all the names without the special characters, but if I'm going to distribute the app someone will write them in their tables.

Can anyone help??

Sorry for my English

Pascolo73
Go to the top of the page
 
theDBguy
post Aug 31 2018, 02:08 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,472
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

I'm not sure I can help you since I don't have a Spanish version of Access or a keyboard with special characters (I don't know how to change my keyboard layout). I just wanted to say I thought if you had a Spanish version of Access and your keyboard can type the special characters, then I assume the search function would just work as normal. If you're saying it doesn't, then it's news to me. Maybe somebody else with a Spanish version and keyboard can confirm it for us, or provide a solution for you.

Good luck and welcome again!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
esancha
post Aug 31 2018, 02:23 PM
Post#3



Posts: 74
Joined: 4-October 14
From: Spain


Hello!

I live in Spain and my keyboard is Spanish, and I have never had problems with the search of any character, Spanish, English or some other languages, so I imagine that the problem is another, particular of your pc

--------------------
Kind Regards
Emilio Sancha
MS Access MVP 2006-2011
http://www.mvp-access.es/emilio/
Go to the top of the page
 
Pascolo73
post Aug 31 2018, 02:31 PM
Post#4



Posts: 21
Joined: 7-July 18



Thank you guys for your fast answer.

The thing is that, for example, one of the names that I have in that table is César (with the special e) and when I type in the searchbox "Cesar" (with a "normal" e) it does not show any results. If you have a multi user app you cannot assure that everyone is going to write every word with the same orthography, so hence my problem.
Go to the top of the page
 
theDBguy
post Aug 31 2018, 02:42 PM
Post#5


Access Wiki and Forums Moderator
Posts: 73,472
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Okay, if I understand it correctly now, you don't want Access to behave normally for your application. Is this correct? If so, there are a couple of approaches to perform fuzzy matching, which is what I think you are looking for but not really sure since I never had to use it for special characters.

The first approach is called "SoundEx," and you can find an example of it on Allen Browne's website. The second approach is called the "Levenshtein Distance" method, and you can find a sample of it in the Code Archive.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Pascolo73
post Aug 31 2018, 03:05 PM
Post#6



Posts: 21
Joined: 7-July 18



Thank you theDBguy.

But the "Levenshtein Distance" is not exactly what I'm looking for. Or if it is, I don't know how to adapt it to my code. The other link that you provided (Allen Browne's website) gives a 404 Not Found error.

I'm posting an example of the form in question (it's in Spanish) to see if you can work something around it. If not, then I will have to set some kind of orthography rule or something.

Attached File(s)
Attached File  SearchClients.zip ( 62.05K )Number of downloads: 5
 
Go to the top of the page
 
theDBguy
post Aug 31 2018, 03:15 PM
Post#7


Access Wiki and Forums Moderator
Posts: 73,472
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I'll take a look at your file in a minute but to fix the URL to Allen's website, change the address on your browser from all uppercase VBA to all lowercase VBA.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Pascolo73
post Aug 31 2018, 03:45 PM
Post#8



Posts: 21
Joined: 7-July 18



Thanks I got it. I'll check it out.
Go to the top of the page
 
Pascolo73
post Aug 31 2018, 03:57 PM
Post#9



Posts: 21
Joined: 7-July 18



Hey theDBguy

Allen Browne's example looks interesting!! I'm not a VBA super expert so I will have to run it several times to fully understand it but I'll give it a try.

Thanks!!
Go to the top of the page
 
theDBguy
post Aug 31 2018, 03:59 PM
Post#10


Access Wiki and Forums Moderator
Posts: 73,472
Joined: 19-June 07
From: SunnySandyEggo


Okay, I gave it a try but the problem is you would want a partial match as well. Unfortunately, both the SoundEx and Levenshtein methods work on comparing the entire string. For example, if you wanted to enter "cesar" and want it to match with "César," then either of these methods will work.

As such, what I am thinking you'll probably need is a "translation" table. For example, you could have the letter "é" in the table with an equivalent letter "e" for a translation, so all you have to do is create a function to replace all matching letters in the client names with the translated version, so then the normal search would work.

Hope it makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Pascolo73
post Aug 31 2018, 04:17 PM
Post#11



Posts: 21
Joined: 7-July 18



Sorry, I do not understand. Remember I'm not en expert on VBA and I'm actually beginning to understand all the commands in Access. I will have to try it when I get home since I'm leaving in 15 minutes.

Thanks for your interest!!

Cheers!
Go to the top of the page
 
esancha
post Sep 1 2018, 05:03 AM
Post#12



Posts: 74
Joined: 4-October 14
From: Spain


Sorry, I had misinterpreted your message, my English is very poor, I actually have that same problem, the same as all those whose language contains characters that are outside the first 255 ASCII characters.

Experience tells me that neither Soundex, Access does not have that function, so we have to create it, nor Levenshtein are sufficiently reliable, so I prefer to use one or another of the following functions, the one (AcentuadoOSinAcentuar) uses regular expressions and is possibly more efficient than the other (SinTildes).

I hope not to contravene any norm, but as I believe that you are Spanish speaker I will also put my answer in Spanish, sure that my answer will be better explained,

Perdón, había malinterpretado tu mensaje, efectivamente tengo ese mismo problema, el mismo que todos aquellos cuyo idioma contiene carácteres que están fuera de los 255 primeros carácteres ASCII.

La experiencia me dice que ni Soundex, Access no dispone de esa función por ello hay que crearla, ni Levenshtein son suficientemente fiables, por ello prefiero utilizar una u otra de las siguientes funciones, la una (AcentuadoOSinAcentuar) utiliza expresiones regulares y es posiblemente mas eficiente que la otra (SinTildes).

CODE
'****************************************************************************
'* AcentuadoOSinAcentuar
'* otro módo de comparar dos cadenas sin preocuparse de los acentos es mediante
'* "Expresiones Regulares" de modo que no es necesario eliminar los acentos de
'* ambos lados, solo hay que convertir una de las dos cadenas a comparar
'* Argumentos: strCadena => cadena a convertir
'* uso: "Numérico" Like AcentuadoOSinAcentuar("Numerico")
'* http://www.mvp-access.es/emilio/
'* Si utilizas este código, respeta la autoría y los créditos
'* ESH 27/01/09 15:03
'***************************************************************************

Public Function AcentuadoOSinAcentuar(strCadena As String) As String

Dim i As Long, _
    Matriz() As String

For i = 1 To Len(strCadena)
   ReDim Preserve Matriz(i - 1)
   Select Case Mid$(strCadena, i, 1)
      Case "a", "á", "â", "ä", "â"
         Matriz(i - 1) = "[aáâäâ]"
      Case "e", "é", "è", "ë", "ê"
         Matriz(i - 1) = "[eéèëê]"
      Case "i", "í", "ì", "ï", "î"
         Matriz(i - 1) = "[iíìïî]"
      Case "o", "ó", "ò", "ö", "ô", "ø"
         Matriz(i - 1) = "[oóòöôø]"
      Case "u", "ú", "ù", "ü", "û"
         Matriz(i - 1) = "[uúùüû]"
      Case Else
         Matriz(i - 1) = Mid$(strCadena, i, 1)
   End Select
Next i

AcentuadoOSinAcentuar = Join(Matriz, vbNullString)
End Function         ' AcentuadoOSinAcentuar

Sub PruebaAcentuadoOSinAcentuar()
Dim strCadena1 As String, _
    strCadena2 As String, _
    sngTimer As Single, _
    i As Long
Dim intArchivo As Integer , _
    strArchivo As String

sngTimer = Timer
strArchivo = "c:\Temp\Prueba.txt"
intArchivo = FreeFile

Open strArchivo For Output As #intArchivo
For i = 1 To 10000
   strCadena1 = "Numérico"
   strCadena2 = "Numerico"
  
   Print #intArchivo, strCadena2 Like AcentuadoOSinAcentuar(strCadena1)
Next i

Close #intArchivo
Debug.Print Timer - sngTimer
End Sub


CODE
'****************************************************************************
'* SinTildes
'* elimina las tildes de la cadena pasada como parámetro
'* Argumentos: strTexto => cadena a la que se han de eliminar las tildes
'* modificando las constantes Con y Sin se puede variar el rango de caracteres
'* afectados por el proceso
'* uso: strTexto = SinTildes(strTexto)
'* http://www.mvp-access.es/emilio/
'* Si utilizas este código, respeta la autoría y los créditos
'* ESH 10/11/05 18:22
'* ESH 22/04/08 18:30
'****************************************************************************

Public Function SinTildes(strTexto As String) As String

Const Con = "áéíóúü"
Const Sin = "aeiouu"

Dim i As Long

For i = 1 To Len(Con)
   strTexto = Replace$(strTexto, Mid$(Con, i, 1), Mid$(Sin, i, 1))
Next i
SinTildes = strTexto
End Function         ' SinTildes

This post has been edited by esancha: Sep 1 2018, 05:04 AM

--------------------
Kind Regards
Emilio Sancha
MS Access MVP 2006-2011
http://www.mvp-access.es/emilio/
Go to the top of the page
 
Pascolo73
post Sep 1 2018, 08:17 AM
Post#13



Posts: 21
Joined: 7-July 18



Thank you very much Emilio. I'll try them and I'll let you know.
Go to the top of the page
 
Pascolo73
post Sep 1 2018, 08:30 AM
Post#14



Posts: 21
Joined: 7-July 18



Guys, it works!!!!

I tried the function AcentuadoOSinAcentuar and it worked very well. I inserted it here:

strBusqueda = "([Nombre Completo] Like ""*" & AcentuadoOSinAcentuar(Me.txtBuscar) & "*"")"

It worked fantastic!!

Thank you very much to all!!

P.S. I did not try the other one (SinTildes)
Go to the top of the page
 
esancha
post Sep 1 2018, 08:42 AM
Post#15



Posts: 74
Joined: 4-October 14
From: Spain


Glad to help you cool.gif

--------------------
Kind Regards
Emilio Sancha
MS Access MVP 2006-2011
http://www.mvp-access.es/emilio/
Go to the top of the page
 
theDBguy
post Sep 1 2018, 08:46 AM
Post#16


Access Wiki and Forums Moderator
Posts: 73,472
Joined: 19-June 07
From: SunnySandyEggo


Oye Emilio,

¡Gracias por ayudar!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
esancha
post Sep 1 2018, 09:35 AM
Post#17



Posts: 74
Joined: 4-October 14
From: Spain


I'm very happy.

--------------------
Kind Regards
Emilio Sancha
MS Access MVP 2006-2011
http://www.mvp-access.es/emilio/
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th October 2018 - 10:43 PM