Dlookup Errors

  • Thread starter Thread starter David
  • Start date Start date
D

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
 
It looks like customer is a string, in which case:

invoicesepmat = DLookup("[SeparateInvoicePerMat]", "custmnt", "[CUSTID]="""
& customer & """")
 
That worked Baz. Thanks! I sure don't understand the need for all the
quotes though. LOL


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

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


David said:
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
Click to expand...
Click to expand...
 
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!)


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


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

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


David said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top