?
Solved

Like Filter and Apostrophes

Posted on 2011-02-18
4
Medium Priority
?
641 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:wellesleydpw
4 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 1600 total points
ID: 34927269
strsql = Chr(34) & "*" & Me!VendorName_Lookup & "*" & Chr(34)

Try that OM Gang
0
 
LVL 50

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 34927504
or try:

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

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


0
 
LVL 75
ID: 34927722
Contrary to popular belief ... single quotes are problematic in Access when used in criteria for exactly the issue shown here.

mx
0
 

Author Closing Comment

by:wellesleydpw
ID: 34927812
both solution work well.  I gave the majority of the points to omgang that solution was posted first.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question