Dlookup Errors

[David]

syntax error (missing operator) in query expression 'CUSTID=2R CONST'.


I am getting the above error when I try to run my code below.

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt", "[CUSTID]=" &
customer)

I get a run time error 2001 You canceled the previous operation with this
code.

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt",
"CUSTID=customer")

customer is a variable that was obtained in the previous code. Any ideas on
why my lookup keeps throwing these errors?

Thanks,

Dave

 

[Baz]

It looks like customer is a string, in which case:

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt", "[CUSTID]="""
& customer & """")

 

[David]

That worked Baz. Thanks! I sure don't understand the need for all the
quotes though. LOL

It looks like customer is a string, in which case:

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt",
"[CUSTID]="""
& customer & """")

syntax error (missing operator) in query expression 'CUSTID=2R CONST'.


I am getting the above error when I try to run my code below.

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt", "[CUSTID]=" &
customer)

I get a run time error 2001 You canceled the previous operation with this
code.

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt",
"CUSTID=customer")

customer is a variable that was obtained in the previous code. Any ideas on
why my lookup keeps throwing these errors?

Thanks,

Dave

 

[Douglas J. Steele]

Since CustID is a Text field, you need to put quotes around the value to
which you're comparing it (customer).

In VBA, when you're building a string, you need to put 2 double quotes in a
row in order to have a double quote appear in the resultant string.

Note that you could also use any of the following instead:

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt", "[CUSTID]=" &
Chr$(34) & customer & Chr$(34))

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt", "[CUSTID]=" &
Chr$(39) & customer & Chr$(39))

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt", "[CUSTID]='" &
customer & "'")

Exagerated for clarity, that last one is

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt", "[CUSTID]= ' "
& customer & " ' ")

Chr$(34) is the representation for ", while Chr$(39) is the representation
for '.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That worked Baz. Thanks! I sure don't understand the need for all the
quotes though. LOL

It looks like customer is a string, in which case:

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt",
"[CUSTID]="""
& customer & """")

syntax error (missing operator) in query expression 'CUSTID=2R CONST'.


I am getting the above error when I try to run my code below.

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt",
"[CUSTID]=" &
customer)

I get a run time error 2001 You canceled the previous operation with
this
code.

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt",
"CUSTID=customer")

customer is a variable that was obtained in the previous code. Any
ideas on
why my lookup keeps throwing these errors?

Thanks,

Dave