CODEWITHSUNDEEP
vbams-accessms-access-2016
Zrhoden
Zrhoden

Reputation: 57

Dlookup Run-time error '3075': Syntax error (missing operator) in query expression

I get:
Run-time error 3075

My code:

Private Sub CustomerID()
    
    Dim ID As Long
    Dim frm As Access.Form
    Set frm = Forms!Frm_JobTicket

    'Look up Customer Name and insert corresponding ID from Qry_CustomerID
    ID = DLookup("Customer_ID", "Qry_CustomerID", "Customer_Name = " & frm("Customer_Name"))
    
    Syteline_Customer_ID = ID

End Sub

I tried to set a form control [Syteline_Customer_ID] = to the Dlookup, but it gave me this same error.

This is my attempt at working around it. [Syteline_Customer_ID] is a text box on Frm_JobTicket.
Qry_CustomerID pulls in 2 fields from Tbl_MasterCustomerList. That table's structure is as follows:

Customer_ID - Number (Integer)
Customer_Name - Short Text
Billing_Address - Short Text
Contact_Person - Short Text
CP_Email - Short Text
CP_Phone - Short Text

I checked Qry_CustomerID is not spelled wrong, and is not missing an underscore, that's just my naming convention.
I tried changing it to just Query1 to call it.

Upvotes: 0

Views: 238

Answers (1)

Parfait
Parfait

Reputation: 107577

Text fields require quote enclosure around literal values. Without quotes, the Access engine assumes you are referencing another field by that literal value. And in your case the space in the name value raised a syntax error. Therefore, in concatenating the form control value, simply enclose it with single quotes:

"Customer_Name = '" & frm("Customer_Name") & "'"

Actually, you do not even need any concatenation as DLookUp can read open form fields with absolute referencing:

"Customer_Name = Forms!Frm_JobTicket!Customer_Name"

Upvotes: 1

Related Questions