Search

[Guest]

Access 2003

Recently we imported some data into Access which contain accented letters,
i.e. "ú"
Field: [Fname] [Lname] French names

By default Jet search (using US Keyboard layout) engines are not intelligent
enough to infer that "pública" is a match for the search string "publica".
Therefore, the literal search for publica (unaccented) will not return any
search results that contain "pública" although there are hundreds of
instances of the word
"pública" in the database.

Is there a way to include boolean search string of "publica" will not return
any search results that contain "pública"?

Thanking you in advance,
Bern

 

[Guest]

Bern:

One way would be to create a table accentedchrs with columns accentedchr and
unaccentedchr, so it would look like this:

accentedchr unaccentedchr
á a
í i
ó o
ú u
and so on

Then you could write a little function:

Public Function Unaccented(ByVal fld)

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strUnaccentedchr As String

strSQL = "SELECT * FROM accentedchrs"

If Not IsNull(fld) Then
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText

With rst
Do While Not .EOF
fld = Replace(fld, .Fields("accentedchr"),
..Fields("unaccentedchr"))
.MoveNext
Loop
End With
End If

Unaccented = fld

End Function

In a query, or elsewhere, compare the return value of the function, passing
the field's value as its argument, with the unaccented string expression:

WHERE Unnaccented(MyField) = "publica"

Ken Sheridan
Stafford, England