Filtering Data

[D.W. Schingenga]

I have created a listbox with customer data. This listbox I filter with a textbox for each column. In the customer name fields we have names like
't Gouwe or "De Hoek". I can filter on a lot of characters but not on the ' and the ". I think that this has to do with the functionality of these characters in Access/VBA. Is there a way I can filter on these characters?

Thanks,

Dirk

 

[grnzbra]

I have no idea if this will work, but in the absence of any suggestions, I'll throw out some thoughts. Since they way you tell Access that it is dealing with an apostrophy rather than something else is to have two apostrophies, perhaps you could create a function that takes in strings and, if it contains an apostrophy, it adds another apostropy.

Then create your query with one field set to the value of the function with the Customer Name as the input to the function. The criteria would have to be handled the same way.

 

[grnzbra]

I just played with it. Here's the function

Public Function Apos(Apin As String) As String
Dim str As String
Dim pos As Integer
Dim poshld As Integer


pos = 1
If InStr(Apin, "'") <> 0 Then
pos = InStr(Apin, "'")
str = Left(Apin, InStr(Apin, "'")) & "'"
poshld = pos
pos = InStr(pos + 1, Apin, "'")
Do While pos > 0
str = str & Mid(Apin, poshld + 1, InStr(poshld + 1, Apin, "'") - (poshld)) & "'"
'
poshld = pos
pos = InStr(pos + 1, Apin, "'")
Loop
str = str & Right(Apin, Len(Apin) - poshld)
Else
str = Apin
End If
Apos = str


End Function

Create a query with one extra field set to this function and the criteria to the field containing the apostrophy. Then set the criteria to this function with the criteria set for whatever you are looking for. It should work whethere you are looking for names with or without apostrophies