• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

DLookup

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
            Else
                GoTo ContinueCode
            End If
        Else
            If (MsgBox("Are you sure you want to add an additional ship to point with this addesss?", vbYesNo)) = vbYes Then
                Resume ContinueCode
            Else
                cmdClose_Click
                Exit Sub
            End If
        End If
    Else
        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.
0
rawilken
Asked:
rawilken
1 Solution
 
Rey Obrero (Capricorn1)Commented:
change this
If DLookup("strSStreet1", "tlkpShipTo", "strSStreet1 = " & Me.txtSStreet1) And

with


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


exploded view

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now