Microsoft Access 2016 - Filter event bugs

We have search boxes that when triggered with an After Update event run the following code segment to filter the list based on what has been entered. The issue being anytime we search for something that includes a ' it errors out into debug. I believe that's most likely because of how we're filtering the data, and the filter is getting confused because there's an extra ' in the code. How can we achieve the same results as listed below, and fix the issue we can't use symbols in the search parameters?

Private Sub ApplyFilter()
  Me!TaskIDTextBox = ""
  Dim filter As String
  filter = "(1=1)"
  If Nz(Me!uxStatus) <> "" Then filter = filter & " and Status='" & Me!uxStatus & "'"
  If Nz(Me!uxPriority) <> "" Then filter = filter & " and Priority='" & Me!uxPriority & "'"
  If Nz(Me!uxEmployee) <> "" Then filter = filter & " and [Assigned To] = '" & Me!uxEmployee & "'"
  If Nz(Me!uxCompany) <> "" Then filter = filter & " and [Company Name] = '" & Me!uxCompany & "'"
  If Nz(Me!NotesFilterText) <> "" Then filter = filter & " and [Customer Notes] like '*" & Me!NotesFilterText & "*'"
  If Nz(Me!InternalNotesFilterText) <> "" Then filter = filter & " and [Internal Notes] like '*" & Me!InternalNotesFilterText & "*'"
  [Worklog-All].Form.filter = filter
  [Worklog-All].Form.FilterOn = True
End Sub

Open in new window

LVL 2
OAC TechnologyProfessional NerdsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Note that you are encapsulating text fields in single quotes in your filter.  To overcome single quotes embedded in your text, you need to replace those single quotes, with two single quotes, like:

If Nz(Me!uxStatus) <> "" Then filter = filter & " and Status='" & Replace(Me!uxStatus, "'", "''") & "'"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Add this to the top part of a standard module - not a class module.

Public Const QUOTE = """"

Open in new window


Then use the QUOTE constant any time you want to embed a double quote.
Private Sub ApplyFilter()
  Me.TaskIDTextBox = ""
  Dim filter As String
  filter = "(1=1)"
  If Nz(Me.uxStatus) <> "" Then filter = filter & " and Status='" & Me!uxStatus & "'"
  If Nz(Me.uxPriority) <> "" Then filter = filter & " and Priority='" & Me!uxPriority & "'"
  If Nz(Me.uxEmployee) <> "" Then filter = filter & " and [Assigned To] = " & QUOTE & Me.uxEmployee & QUOTE
  If Nz(Me.uxCompany) <> "" Then filter = filter & " and [Company Name] = " & QUOTE & Me.uxCompany & QUOTE
  If Nz(Me.NotesFilterText) <> "" Then filter = filter & " and [Customer Notes] like " & QUOTE & "*" & MeNotesFilterText & "*" & QUOTE
  If Nz(Me.InternalNotesFilterText) <> "" Then filter = filter & " and [Internal Notes] like" & QUOTE & "*" & Me.InternalNotesFilterText & "*" & QUOTE
  [Worklog-All].Form.filter = filter
  [Worklog-All].Form.FilterOn = True
End Sub
0
Dale FyeOwner, Developing Solutions LLCCommented:
I actually use a function to wrap items in quotes:
Public Function fnQuotes(QuoteWhat as VARIANT, optional QuoteWith as string = """") as String

    if IsNull(QuoteWhat) then
        fnQuotes = "NULL"
    Else
        fnQuotes = QuoteWith & Replace(QuoteWhat, QuoteWith, QuoteWith & QuoteWith) & QuoteWith
    end if

End Function

Open in new window

You can then write your code like:
  If Nz(Me.uxStatus) <> "" Then filter = filter & " and Status=" & fnQuotes(Me!uxStatus)

Open in new window

Which is much shorter, and easier to read.

I cannot explain it, but Access (don't know whether it is VBA, JET, or ACE) actually interprets these doublets ('', "", and even &&) as a single instance of that character.
0
OAC TechnologyProfessional NerdsAuthor Commented:
Great, didn't know about the double ' ' workaround. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.