Semicolon Error 1

[nag9127]

The following code is producing a "missing semicolon at end of SQL statement" error. I can't seem to figure it out so I am asking for some help and possibly an explanation. Thanks in advance!

Dim strSQL As String

strSQL = ""
strSQL = strSQL & "INSERT INTO ProductionBatchDetailTable"
strSQL = strSQL & "([BatchID], [ItemUsed], [ItemUsedDescription], [QuantityUsed])"
strSQL = strSQL & "VALUES ('" & BatID & "','" & ItemUsed & "','" & Expr1 & "','" & QtyUsed & "')"
DoCmd.RunSQL strSQL

 

[genomon]

Try this:
strSQL = strSQL & "VALUES ('" & BatID & "','" & ItemUsed & "','" & Expr1 & "','" & QtyUsed & "');"

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[nag9127]

Tried it and also tried at end of insert statement with no change.

 

[MikeC14081972]

Try using

codedb.execute "INSERT INTO ProductionBatchDetailTable " _
             & "([BatchID], [ItemUsed], [ItemUsedDescription], [QuantityUsed]) " _
             & "VALUES ('" & BatID & "','" & ItemUsed & "','" & Expr1 & "','" & QtyUsed & "')",dbfailonerror

 

[genomon]

Please note the space before the VALUES clause above!

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[nag9127]

geonomon:

What space are you referring to. In your post or the in the post below yours?

 

[genomon]

[QuantityUsed]) " _
             & "VALUES ('" & BatID & "','" & ItemUsed & "','" & Expr1 & "','" & QtyUsed & "')",dbfailonerror

There is a space between [QuantityUsed]) " and "VALUES in Mike's post, but not your original code. That (hopefully) is it. If you are not executing the query with JET then you don't need the semicolon.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[PHV]

Could any of BatID,ItemUsed,Expr1 and QtyUsed contain an apostrophe ?
If so, use something like this:

strSQL = strSQL & "VALUES ('" & BatID & "','" & ItemUsed & "','" & Replace(Expr1, "'", "''") & "','" & QtyUsed & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nag9127]

PHV:

I invoked your solution and it works perfectly. Not only does it clear the apostrophe allowing the INSERT command to run properly, but the inserted field is left in its original state when inserted in the table, without the double '. Can you explain how the data was only changed during the routine and not permanently in the table where it was inserted. Is this all a function of the replace command and how it was placed in the INSERT statement? I guess this is what replace is designed to do. Is that correct? Just trying to understand!

 

[genomon]

The two-single-quotes is a database convention, recognized by the database engine. Smoke & mirrors...

"Business conventions are important because they demonstrate how many people a company can operate without."