I am running access 2000 for a fishing charter database.
On a form I have a simple search box that searches for clients by their last name. Anyone that has an ' in their name produces the following error report form access "Runtime error 3077 - Syntax error (missing operator) in expression.

Below is the code that runs the search combo box.

Any help would be greatly appreciated.

Private Sub Combo332_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Last Name] = '" & Me![Combo332] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Stephen

Dear fren,

hope this will help...

Private Sub Combo332_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Last Name] = '" & filterStr(Me![Combo332]) & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Function filterStr(ByVal str As String) As String
    Dim i As Integer
    Dim temp_str As String

    temp_str = ""
    For i = 1 To Len(str)
        If Mid(str, i, 1) = "'" Then
            temp_str = temp_str & "''"
        Else
            temp_str = temp_str & Mid(str, i, 1)
        End If
    Next
    filterStr = temp_str
End Function


snowbelly

I thought I would post a similar problem. The code I've been running for some time recently started producing the same Runtime error. ( Runtime error 3077 - Syntax error (missing operator) in expression) This occur on data import from Excel. I'm not really sure why it's bugging out, but if anyone has any fixes it would be much appreciated.

Private Sub btnImportEmail_Click()
    Dim pathName As String, fileName As String, tableName As String, answer As Variant
    pathName = "C:\Documents and Settings\Master\My Documents\PETER\email_data\data\"
    'pathName = "C:\Documents and Settings\Master\My Documents\PETER\email_data\data\"
    'pathName = "C:\Documents and Settings\Master\My Documents\PETER\email_data\data\"
    tableName = "tblImportRaw"
    fileName = Dir(pathName)
    answer = vbOK

    DoCmd.SetWarnings False

    Do While fileName <> "" 'And answer = vbOK
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, tableName, pathName & fileName, True
    'now move records from tblImport to tblClients, except for duplicates
        DoCmd.RunSQL "DELETE * FROM tblImportRaw WHERE ([Admin Email] Is Null) OR ([Admin Email] Like 'no.valid.email@*') OR (([Admin City] Not Like '*US') And ([Admin City] Not Like '*United States*') And ([Admin City] Not Like '* ?? #####') And ([Admin City] Not Like '*CA') And ([Admin City] Not Like '*Canada') And ([Admin City] Not Like '* USA'));"
        DoCmd.OpenQuery "qryTransferImports"
        DoCmd.OpenQuery "qryDeleteNamezeroEtc"
        DoCmd.OpenQuery "qry4FilterName"
        DoCmd.OpenQuery "qryIdentifyUS"
        DoCmd.OpenQuery "qryDeleteNonUS"
        separate_extension_Click
        add_state_Click
        mergeImportEmail_click
        DoCmd.RunSQL "DELETE * FROM tblImportRaw"

        'DoCmd.OpenQuery "qry5DeleteUnsuitable"
        Kill pathName & fileName
        fileName = Dir()
' answer = MsgBox("Import next file?", vbOKCancel)
    Loop
End Sub