MS Access 2010 VBA runtime error 3075 (searching with apostrophes)

Question

The answer is probably really simple - I just can't come up with the right search term, I suspect.

I have a form that opens another form, displaying any employee record that matches the search as entered You can search by surname, given name, or employee ID (using separate buttons); it gives you a little message box if your search turns up nothing.

The code works fine, except for the usual problem with handling apostrophes in names ("O'Neill," "O'Brien," etc.) I found a really simple apostrophe handling function, but when I try to use the function in the search query it still throws up a 3075 runtime error, and I'm not sure why. It only throws up the runtime error with apostrophe-containing searches, so I feel like the function maybe isn't doing what I think it is.

I am happy to entertain solutions that involve "using this function but adding more quotation marks (or whatever)" as well as whole new ideas. I'd prefer to use something like this function, though, because it's so small and thus it'll be much faster and cleaner to replace the search-by-name code each place that it appears.

This is the code that works fine:

Private Sub btnSearchSurname_Click()

Dim frm As Form
Dim strSearch As String

strSearch = "[List_Employees.Surname] like '" & Me.EmpSurname & "*'"
strSearch = strSearch & " AND [CurrentEmployee] = " & True
DoCmd.OpenForm "Employee_Entry_Extended_Certs", , , strSearch, , acHidden
Set frm = Forms("Employee_Entry_Extended_Certs")
If frm.Recordset.RecordCount > 0 Then
    frm.Visible = True
    Else
    MsgBox ("Employee not found.  Try the 'all' button to see if they're inactive.  If that doesn't work, please check for typos and try again.")
    DoCmd.Close acForm, "Employee_Entry_Extended_Certs"
    Call OpenPayrollCloseRest
End If

DoCmd.Close acForm, "Find_An_Employee"

I'm trying to use this simple public function to handle apostrophes:

Public Function adhHandleQuotes(ByVal varValue As Variant, Optional Delimiter As String = "'") As Variant
    ' Replace all instances of a string delimiter with TWO instances,
    ' thereby handling the darned quote issue once and for all. Also,
    ' surround the string with the delimiter, as well.

    ' Returns Null if the String was Null, otherwise
    ' returns the String with all instances of strDelimiter
    ' replaced with two of each.

    adhHandleQuotes = strDelimiter & Replace(varValue, strDelimiter, strDelimiter & strDelimiter) & strDelimiter
End Function

I modified the search code to use the function by inserting three lines lines in place of the first "strSearch = " line:

Dim strSearch As String
Dim strTerm As String

strTerm = adhHandleQuotes(Me.EmpSurname)
strSearch = "[List_Employees.Surname] like '" & strTerm & "*'"
strSearch = strSearch & " AND [CurrentEmployee] = " & True
DoCmd.OpenForm "Employee_Entry_Extended_Certs", , , strSearch, , acHidden

And this is the runtime error dialogue box:

Runtime Error '3075': Syntax error (missing operator) in query expression '[List_Employees.Surname] like 'o'cal*' AND [CurrentEmployee]=True'

Your function adhHandleQuotes is using strDelimiter in the code, but the optional parameter's name is Delimiter. Have you checked what adhHandleQuotes(Me.EmpSurname) returns? — xificurC

PaulFrancis PaulFrancis · Accepted Answer · 2014-09-17T15:45:54

Why do you even need a function? Just simply incorporate a Double Quotes, my hack is to use Chr(34).

Private Sub btnSearchSurname_Click()

    Dim frm As Form
    Dim strSearch As String

    strSearch = "[List_Employees.Surname] Like " & Chr(34) & Me.EmpSurname & "*" & Chr(34)
    strSearch = strSearch & " AND [CurrentEmployee] = True"

    DoCmd.OpenForm "Employee_Entry_Extended_Certs", , , strSearch, , acHidden

    Set frm = Forms("Employee_Entry_Extended_Certs")
    If frm.Recordset.RecordCount > 0 Then
        frm.Visible = True
    Else
        MsgBox ("Employee not found.  Try the 'all' button to see if they're inactive.  If that doesn't work, please check for typos and try again.")
        DoCmd.Close acForm, "Employee_Entry_Extended_Certs"
        Call OpenPayrollCloseRest
    End If

    DoCmd.Close acForm, "Find_An_Employee"
End Sub

MS Access 2010 VBA runtime error 3075 (searching with apostrophes)

2 Answers