I have  a dlookup in vba that is failing. I have parsed it several times and the error is 3075 missing operator.
Here is the whole section of code...

    If Me.txtSCompany & "" <> "" Or Me.txtSStreet1 & "" <> "" Or Me.txtSZip & "" <> "" Then
        If CheckedName = False Then
            If DLookup("strSStreet1", "tlkpShipTo", "strSStreet1 = " & Me.txtSStreet1) And DLookup("strSZip", "tlkpShipTo", "strSZip = '" & Me.txtSZip & "'") Then
                MsgBox "There already is a location with this address in the system." & vbNewLine & vbNewLine & "Please look at the list of existing ship to points and confirm your choice.", vbExclamation
                DoCmd.RunCommand acCmdSaveRecord
                DoCmd.OpenForm "frmShipToList", , , "strSStreet1 = '" & Me.txtSStreet1 & "' and  strSZip = '" & Me.txtSZip & "'"
                CheckedName = True
                Exit Sub
                GoTo ContinueCode
            End If
            If (MsgBox("Are you sure you want to add an additional ship to point with this addesss?", vbYesNo)) = vbYes Then
                Resume ContinueCode
                Exit Sub
            End If
        End If
        MsgBox "Please enter a name, address and zip for this ship to point.", vbExclamation
        Exit Sub
    End If

Looking at line 3...
Now normally the strSStreet1 = txtSStreet1 would be written as "strSStreet1 = '" & Me.txtSStreet1 * "'" but for some reason the program throws a type mismatch error if I include the single quotes. When I remove them, the error is that the operator is missing.
The DLookup for the strSZip works fine as is!!! I have run each DLookup separately. The only problem is with the strStreet1 DLookup.
Who is Participating?
Helen FeddemaConnect With a Mentor Commented:
Instead of using long expressions referencing controls, try creating a String expression with the values from the controls, and writing it to the Immediate Window using Debug.Print.  This is helpful in debugging problems of this sort.
Rey Obrero (Capricorn1)Commented:
change this
If DLookup("strSStreet1", "tlkpShipTo", "strSStreet1 = " & Me.txtSStreet1) And


If DLookup("strSStreet1", "tlkpShipTo", "strSStreet1 = '" & Me.txtSStreet1 & "'") And

exploded view

If DLookup("strSStreet1", "tlkpShipTo", "strSStreet1 = ' " & Me.txtSStreet1 & " ' ") And
Is there possibly a " or ' in strStreet1?
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.