Solved

DLookup

Posted on 2013-01-17
3
498 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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Form button in access 2 37
Access Runtime 2010 Error 17 30
Set focus on next field when character count = 5 9 12
Update Access FrontEnd by Version # 9 21
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now