SQL VBA Where Clause Issue

[thescottsman]

Hi All

I have done this before but having a blank moment. I have constructed this vba sqk statement and it will not work. I believe it is due to the WHERE Clause.

Can anyone see any issues with it?

UpdateOrderSQL = "UPDATE tbltest " & _
"SET [Supplier]='" & Me.Supplier & "',
"WHERE [OrderNumber]='" & Me.OrderNumber & "' AND [PartNumber]='" & Me.PartNumber & "' "

Thanks for your help

 

[Tieval]

Surely you want to set the supplier to me.supplier

 

[CJ_London]

looks like you are missing a double quote and ampersand at the end of the SET line and a space before the WHERE

Also all are being treated as strings - is this correct?

 

[thescottsman]

Hi

That is what it is doing

It just I want to update the supplier for the order number and the part number in the table

 

[CJ_London]

also looks like you have a superfluous comma at the end of the SET line

 

[thescottsman]

Hi

I have altered those and it still does not work

 

[Minty]

You'll need to post up exactly what you've used. Put it in code tags don't re-type it, copy and paste it.
And as already asked are Where values text or numeric ?

 

[MarlaC]

You also are missing the closing semicolon at the end of your SQL statement.

 

[Galaxiom]

You also are missing the closing semicolon at the end of your SQL statement.

The semicolon is optional for single commands, which is all Access can process anyway.

 

[Galaxiom]

Do a Debug.Print of the command string after the concatenation of the variables. The errors usually become obvious.

 

[MarlaC]

The semicolon is optional for single commands, which is all Access can process anyway.

In my experience, some versions handle that more gracefully than others and it's better to be prepared for the stricter case. YMMV.

 

[Solo712]

Do a Debug.Print of the command string after the concatenation of the variables. The errors usually become obvious.

In this case, it will likely show a missing space before WHERE, which is the most likely cause of the trouble.

Best,
Jiri