SQL Syntax Problem

[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.

 

[Stuart McCall]

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.

All I can spot is that the words VALUES and WHERE ought to be preceded by
space characters.

 

[Dirk Goldgar]

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.

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.

 

[Sylvain Lafontaine]

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.

 

[Dirk Goldgar]

Probably because you're trying to mix an INSERT statement with a WHERE
condition.

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