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?
 
Dale FyeConnect With a Mentor Commented:
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
 
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 FyeCommented:
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
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.

All Courses

From novice to tech pro — start learning today.