Update Query

[CMitchell]

Hi,

Having already established values for varInvStart and varInvFinish, I wish to use these values in the following query:

For varX = varInvStart To varInvFinish
DoCmd.RunSQL "UPDATE Data SET PrintOrderConfirmation = 1 WHERE InvoiceNumber = varX;"
Next varX

When I run this query, Access still asks for the value of varX…

I would be very grateful if anyone can suggest what the correct syntax is.

Thanks
Chris

 

[KevGooss]

I would say the problem has to do with the fact that the term varX that you are using is enclosed within the quotations. Access cannot discern that this is a variable that you want to compare.try something like this:

"...WHERE InvoiceNumber = " & varX & ";"

I'm fairly sure this should work for you

Kevin

 

[CMitchell]

Thanks very much Kevin, That worked fine. Much obliged.

I was wondering if you (or anyone else here) would have any Idea where I am going wrong with this...

DoCmd.OpenReport "Print Order Confirmation", acViewPreview, , "[InvoiceNumber] = '" & varX & "'"

I was hoping that the wherecondition would provide varX as the appropriate invoice number when opening the report but it doesn't work...

quite frankly - I'm stumped.
Any help would be appreciated...

Thanks
Chris

 

[Neal]

I'm not sure, but I think you can just write:

DoCmd.OpenReport "Print Order Confirmation", acViewPreview, , "[InvoiceNumber] = varX"

 

[Fornatian]

To add to Neals suggestion I think it will only work if you varX outside the quotes but without single quotes(single quotes tend to only be used for text).
Like:

DoCmd.OpenReport "Print Order Confirmation", acViewPreview, , "[InvoiceNumber] = " & varX

Ian

 

[CMitchell]

Thanks.

Neal was on the right track and Fornatian
was spot on.

The answer seems obvious to me now - but you know how sometimes you can't see the wood for the trees !!

Much obliged to you both.
Regards
Chris