RunSql syntax error?

  • Thread starter Thread starter G Lam
  • Start date Start date
G

G Lam

I got syntax error for the following codes in the Click_Ok event:

DoCmd.RunSQL "INSERT INTO tblEdPartHis (EdHisOrdNbr , EdHisOldPnbr,
EdHisNewPnbr)" & _
"values( Ordnbr, " & Vvold & ", PartNbr ) "

The error msg: Syntax error (missing operator in query expression 'UC-1034
Old' )
I think it refer to the Vvold variable.

Ordnbr and PartNbr are two fields in the current form, while the Vvold is a
variable that has old PartNbr value. When I place the cursor on each of them
in the debug window, all of them shown the right value. But I got this
error.

I tried to assign all three values in variables, but got the same error.
Any idea?

Thank you
Gary
 
I got syntax error for the following codes in the Click_Ok event:

DoCmd.RunSQL "INSERT INTO tblEdPartHis (EdHisOrdNbr , EdHisOldPnbr,
EdHisNewPnbr)" & _
"values( Ordnbr, " & Vvold & ", PartNbr ) "

The error msg: Syntax error (missing operator in query expression 'UC-1034
Old' )
I think it refer to the Vvold variable.

Ordnbr and PartNbr are two fields in the current form, while the Vvold is a
variable that has old PartNbr value. When I place the cursor on each of them
in the debug window, all of them shown the right value. But I got this
error.

I tried to assign all three values in variables, but got the same error.
Any idea?

Thank you
Gary
Click to expand...

You need to enclose the value of Vvold into single parentheses, since
it is a string field:

"values( Ordnbr, '" & Vvold & "', PartNbr ) "

Notice the single quotes after "Ordnbr, " and before " , PartNbr"
Without the single quotes, the SQL string after resolution of Vvold
looks like this in your example:

values(Ordnbr, UC-1034 Old, PartNbr)

instead of

values(Ordnbr, 'UC-1034 Old', PartNbr)

Access just tries to do what you tell it to do... :-)

HTH
Matthias Kläy
 
Matthias,
Thanks. That works. I always get confused by these single quote, double
quote, & and #.
Luckily, you guys are here and give out great helps.
Gary
 
Back
Top