MSAccess - Why doesn't this concatenated SQL Update Statement not work?

[thereds]

SQL = "UPDATE OrderProcessing SET OrderProcessing.Complete = 1 Where CarID = " + lngVId

I get a type mismatch error as in the variable SQL (of type string) does not get that whole update query string.

lngVid is datatype Variant/Long
CarID is Datatype Number

If i remove the variable at the end and hardcode it to end like...Where CardID = 222", then it works.

Also if instead of lngVId, I put in some variable of datatype Long, it doesn't work.

Can someone explain why this is happening?

 

[kamper]

First you're gonna have to tell us what language you're using... <edit> whoops, must not have read the title very closely </edit>

But I'd guess that whatever it is, it can't handle concatentation of string and numeric types. You're gonna have to find a function to turn a number into a string.

 

[KLin]

Originally posted by: thereds
SQL = "UPDATE OrderProcessing SET OrderProcessing.Complete = 1 Where CarID = " + lngVId

I get a type mismatch error as in the variable SQL (of type string) does not get that whole update query string.

lngVid is datatype Variant/Long
CarID is Datatype Number

If i remove the variable at the end and hardcode it to end like...Where CardID = 222", then it works.

Also if instead of lngVId, I put in some variable of datatype Long, it doesn't work.

Can someone explain why this is happening?

you have to use a &amp; instead of the +. + is used in T-SQL for concatenation, but not VBA.

 

[Firus]

I don't know if MSaccess has it, but try using the convert function...
SQL = "UPDATE OrderProcessing SET OrderProcessing.Complete = 1 Where CarID = " + convert(number, lngVId) I think thats how it works.

edit: Spelling

 

[KLin]

I just tested it out. It is the + causing the problem. Replace the + with a &amp; and it should work.

 

[thereds]

Thx guys.