SQL Syntax Error. 1

[Gerbers404]

Hello, I am recieveing an Syntax error when I try to run this SQL code. Can anyone see the problem? Thanks for your help.

The problem is somewhere in the bolded area.

mySQLInsert = "INSERT INTO Xfer (JN, Chamb_ID) (SELECT JN, Chamb_ID FROM tblSpecCondCurrent WHERE JN = '" & Job & "' AND Chamb_ID = " & Chamber & " INSERT INTO tblSpecCondArchive (JN, Chamb_ID) SELECT JN, Chamb_ID FROM Xfer; DROP TABLE Xfer; DELETE FROM tblSpecCondCurrent WHERE JN = '" & Job & "' AND Chamb_ID = " & Chamber & ";"
Gerbers404

 

[zemp]

Here is the syntax for the Insert statement.

INSERT INTO [Table1] ([Field1],[Field2],[Field3]) Values (<intVaLue1>,'<strValue2>','<strValue3>');

I am not sure if you can combine a Select statement with the insert statement, never tried it. Make sure that you place the single quote around string values. Thanks and Good Luck!

zemp

 

[MarkSweetland]

Without knowing the exact error you're receiving here's my guess:

If the variable Chamber has no value (i.e. zero-length string or NULL) then the SQL statement will be constructed incorrectly after it's all said and done:

...AND Chamb_ID = ); INSERT INTO ...

JN will equal a zero length string if the variable is empty since it is enclosed in single quotes and not affect the syntax of the SQL statement.





Mark

 

[zemp]

Use the line

Debug.print mySQLInsert

to see exactly what your insert statement looks like after it is constructed. Thanks and Good Luck!

zemp

 

[Gerbers404]

Here is the way the String is Constructed ( I removed the other parts of the statement for now, as I KNOW this part isn't working

&quot;INSERT INTO Xfer (JN, Chamb_ID) (SELECT JN, Chamb_ID FROM tblSpecCondCurrent WHERE JN = '0303011' AND Chamb_ID = 3);&quot;

Looks to me like it concatenated correctly, but VBA sure doesn't seem to like it.
Gerbers404

 

[MarkSweetland]

Don't think you need the parens around the SELECT either:

&quot;INSERT INTO Xfer (JN, Chamb_ID) SELECT JN, Chamb_ID FROM tblSpecCondCurrent WHERE JN = '0303011' AND Chamb_ID = 3;&quot;



Mark

 

[zemp]

I believe you do need the Parens and the key word 'Values', try,

&quot;INSERT INTO Xfer (JN, Chamb_ID) Values (SELECT JN, Chamb_ID FROM tblSpecCondCurrent WHERE JN = '0303011' AND Chamb_ID = 3);&quot;

Also what are the values for JN and Chamb_ID. I would go even further and split the two statememnts.

rs.open &quot;SELECT JN, Chamb_ID FROM tblSpecCondCurrent WHERE JN = '0303011' AND Chamb_ID = 3;&quot;,...

&quot;INSERT INTO Xfer (JN, Chamb_ID) Values (rs![JN], rs![Chamb_ID]);&quot;

Also place single quotes if the values are strings.

&quot;INSERT INTO Xfer (JN, Chamb_ID) Values ('rs![JN]', 'rs![Chamb_ID]');&quot;
Thanks and Good Luck!

zemp

 

[Gerbers404]

Ahh!!!!!! That seemed to be the entire problem. Thanks alot Mark! I really appreciate it! Gerbers404

 

[Gerbers404]

Thank you as well, zemp! My day just got much better Gerbers404