Question CurrentDb.Execute

[Barry.dunnage]

hi all

Can you help with this one please?

This works CurrentDb.Execuite "UPDATE Table1 SET Table1.Status = 2 WHERE Table1.orderNumber ='632';"

This wont work CurrentDb.Execuite "UPDATE Table1 SET Table1.Status = 2 WHERE Table1.orderNumber = varItem1;"

 

[nanscombe]

The value of varItem1 should be appended to the string rather than the word varItem1 placed inside it.

If varItem1 is a number:

CurrentDb.Execute "UPDATE Table1 SET Table1.Status = 2 WHERE Table1.orderNumber = " & varItem1

If varItem1 is a string:

CurrentDb.Execute "UPDATE Table1 SET Table1.Status = 2 WHERE Table1.orderNumber = '" & varItem1 & "'"

 

[Galaxiom]

Execute has no awareness of Access objects such as forms and controls or variables. These values must be concatenated int o the SQL string.

"UPDATE Table1 SET Table1.Status = 2 WHERE Table1.orderNumber =" & varItem1 & ";"

Or if the varItem1 is a string.

"UPDATE Table1 SET Table1.Status = 2 WHERE Table1.orderNumber ='" & varItem1 & "';"

(The semicolon terminator isn't essential.)

 

[Galaxiom]

Snap. ...............

 

[nanscombe]

I probably only got in first due to the time lag from Aus.

 

[Barry.dunnage]

Hi Thanks for the prompt replies

I have done as you said. now getting run time error 3464 Data type mismatch in criteria expression

any ideas

 

[nanscombe]

It sounds like one is a string the other a number.

Either:

orderNumber is a number and you are passing it a string.

Or

orderNumber is a string and you are passing it a number.

That's why we included two ways of making the string for CurrentDb.execute.

If "CurrentDb.Execuite "UPDATE Table1 SET Table1.Status = 2 WHERE Table1.orderNumber ='632';" works I reckon it is expecting it in the String form.

CurrentDb.Execute "UPDATE Table1 SET Table1.Status = 2 WHERE Table1.orderNumber = '" & varItem1 & "'"

 

[Barry.dunnage]

Thanks for your help nanscomb
It likes being a string have a nice day.

Regards

Barry