Solved

DLookup

Posted on 2013-01-17
3
519 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

756 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