Like Filter and Apostrophes

I have combo box VendorName_Lookup in which a user can type all or part of a Vendor Name which defines a filter on the form to filter for those records where that value is in either of two fields [Vendor] or [DBA].  The problem I run into is when there is an apostrophe in the vendor name (e.g. Sam's).
See code below.
Please help.
Jeff



 
Private Sub VendorName_Lookup_AfterUpdate()

Dim FilterCriteria As String
Dim strsql As String
strsql = "'*" & Me!VendorName_Lookup & "*'"
FilterCriteria = "[Vendor] Like " & strsql & " or [DBA] Like " & strsql
Me.Vendor_sf_Index.Form.Filter = FilterCriteria
Me.Vendor_sf_Index.Form.FilterOn = True

DoCmd.SearchForRecord , "", acFirst, "[Vendor] = " & "'" & Screen.ActiveControl & "'"
End Sub

Open in new window

wellesleydpwAsked:
Who is Participating?
 
omgangConnect With a Mentor Commented:
strsql = Chr(34) & "*" & Me!VendorName_Lookup & "*" & Chr(34)

Try that OM Gang
0
 
Dale FyeConnect With a Mentor Commented:
or try:

strsql = "'*" & Replace(Me!VendorName_Lookup, "'", "''") & "*'"

This will replace single instances of an apostrophe (') with doublets ('').


0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Contrary to popular belief ... single quotes are problematic in Access when used in criteria for exactly the issue shown here.

mx
0
 
wellesleydpwAuthor Commented:
both solution work well.  I gave the majority of the points to omgang that solution was posted first.
0
All Courses

From novice to tech pro — start learning today.