Solved

DLookup

Posted on 2013-01-17
3
506 Views
Last Modified: 2013-01-17
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
Comment
Question by:rawilken
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38788542
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 38788555
Is there possibly a " or ' in strStreet1?
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 150 total points
ID: 38788747
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question