Variables in docmd.runsql command

[David]

Here is my command I'm trying to run.

DoCmd.RunSQL ("UPDATE OrderEntry LEFT JOIN Tickets ON OrderEntry.ORDERNUMBER
= Tickets.OrderNumber SET Tickets.InvoiceNumber =
DMax('INVOICENUMBER','INVOICES'), Tickets.Invoiced = Yes WHERE
(((Tickets.Invoiced)= No) And ((OrderEntry.CUSTOMERCODE)= rsCustomerCode)
And ((OrderEntry.SHIPTO) = rsAddress) And ((Tickets.Delivered) = Yes) And
((Tickets.Cancelled) = No)) Or (((Tickets.Invoiced) = No) And
((OrderEntry.CUSTOMERCODE) = rsCustomerCode) And ((OrderEntry.SHIPFROM) =
rsAddress) And ((Tickets.Delivered) = Yes) And ((Tickets.Cancelled) = No))")

The variables are
rscustomercode
rsaddress
rsmaterial

I tested these variables and they do contain the required data. For some
reason when I run the command it prompts for me to fill in the variable
data. I'm sure its a syntax error. What am I doing wrong?

Thanks,

Dave

 

[Bill Mosca, MS Access MVP]

David

Move the variables out of the SQL syntax and concatenate them like this:
"And ((OrderEntry.CUSTOMERCODE)=" & rsCustomerCode & ") "

Make sure you include single quotes around text and #'s around dates.
"And MyDate=#" & rsDate & "#)"