SQL Syntax Problem

  • Thread starter Thread starter Bill Phillips
  • Start date Start date
B

Bill Phillips

I am having a syntax problem that seemes easy but I don't understand it. I
have the following SQL statement:

DoCmd.RunSQL "INSERT INTO tblDiscrepantItems (prod, qtyonhand, qtycnt,
SXExt, CountExt, DollarDiscrepant, PercentDiscrepant)" & _

"VALUES ('" & rs1!prod & "', " & rs1!qtyonhand & ", " & rs1!qtycnt & ", " &
rs1!SXExt & ", " & rs1!CountExt & ", " & dblDollarDiscrepant & ", " &
dblPercentDiscrepant & ")" & _

"WHERE ((" & dblMinDollar & " <= " & absDollarDisc & ") and (" &
absDollarDisc & "< " & dblMaxDollar & "));"

I keep getting the following error: Run-time error 3137. Missing semicolon
(;) at end of SQL statement. I am inserting these records into a predefined
table so I'm sure what the error is.

Thanks in advance.
 
Bill Phillips said:
I am having a syntax problem that seemes easy but I don't understand it. I
have the following SQL statement:

DoCmd.RunSQL "INSERT INTO tblDiscrepantItems (prod, qtyonhand, qtycnt,
SXExt, CountExt, DollarDiscrepant, PercentDiscrepant)" & _

"VALUES ('" & rs1!prod & "', " & rs1!qtyonhand & ", " & rs1!qtycnt & ", "
&
rs1!SXExt & ", " & rs1!CountExt & ", " & dblDollarDiscrepant & ", " &
dblPercentDiscrepant & ")" & _

"WHERE ((" & dblMinDollar & " <= " & absDollarDisc & ") and (" &
absDollarDisc & "< " & dblMaxDollar & "));"

I keep getting the following error: Run-time error 3137. Missing semicolon
(;) at end of SQL statement. I am inserting these records into a
predefined
table so I'm sure what the error is.

Thanks in advance.
Click to expand...

All I can spot is that the words VALUES and WHERE ought to be preceded by
space characters.
 
Bill Phillips said:
I am having a syntax problem that seemes easy but I don't understand it. I
have the following SQL statement:

DoCmd.RunSQL "INSERT INTO tblDiscrepantItems (prod, qtyonhand, qtycnt,
SXExt, CountExt, DollarDiscrepant, PercentDiscrepant)" & _

"VALUES ('" & rs1!prod & "', " & rs1!qtyonhand & ", " & rs1!qtycnt & ", "
&
rs1!SXExt & ", " & rs1!CountExt & ", " & dblDollarDiscrepant & ", " &
dblPercentDiscrepant & ")" & _

"WHERE ((" & dblMinDollar & " <= " & absDollarDisc & ") and (" &
absDollarDisc & "< " & dblMaxDollar & "));"

I keep getting the following error: Run-time error 3137. Missing semicolon
(;) at end of SQL statement. I am inserting these records into a
predefined
table so I'm sure what the error is.
Click to expand...


I don't see anything obviously wrong. Maybe one of the values you're
building into the statement is Null. I suggest you assign the statement to
a string variable and check the value of that variable before executing it.
More often than not, examining the actual SQL statement as built shows you
immediately where the problem is.
 
Probably because you're trying to mix an INSERT statement with a WHERE
condition. Remove the Where condition and you should be OK. If not, store
the result of the string concatenations to a message box in order to see if
there is any empty value.
 
Sylvain Lafontaine said:
Probably because you're trying to mix an INSERT statement with a WHERE
condition.
Click to expand...

Good catch! I'm embarrassed I didn't notice that.
 
Back
Top