SQL issue (1 Viewer)

[andy1968]

I have a simple problem that has me stumped.



I'm trying to add data to a table with an input into Sql string. I get a syntax error missing operator in the query expression '25r1'.


25r1 is the value of strCN.


lngID is a number, the other items are strings.

strSql = "INSERT INTO [tblSubmittalInfo] ( [SerialNumber], [ContractorNumber], [Reviewer] )Values(" & lngID & ", " & strCN & ", " & Me.Reviewer & " );"
                Debug.Print strSql

From the immediate window I get strSql =

INSERT INTO [tblSubmittalInfo] ( [SerialNumber], [ContractorNumber], [Reviewer] )Values(397, 25r1, Roberto Salinas );

 

[arnelgp]

trSql = "INSERT INTO [tblSubmittalInfo] ( [SerialNumber], [ContractorNumber], [Reviewer] )Values(" & lngID & ", " & Chr(34) & strCN & Chr(34) & ", " & Chr(34) & Me.Reviewer & Chr(34) & " );"
                Debug.Print strSql

 

[theDBguy]

Hi. Try delimiting your string values. For example:

...Values(" & lngID & ", '" & strCN & "', '" & Me.Reviewer & "' );"

Edit: Oops, too slow...

 

[andy1968]

Great - Work like a charm. Thanks

 

[andy1968]

New similar issue.



This INSERT INTO is not working.



The problem seems to be with the Note field; if I change Note to a different field in the tblSubmittalNotes the sql works just fine.

strSql = "INSERT INTO [tblSubmittalNotes] ( SerialNumber, Note) " & _
                '    "SELECT " & lngID & " As SerialNumber, Note " & _
                  '   "FROM [tblSubmittalNotes] WHERE SerialNumber = " & ingOldID & ";"


 DBEngine(0)(0).Execute strSql, dbFailOnError

The Note field is a LongText

 

[theDBguy]

New similar issue.

This INSERT INTO is not working.

The problem seems to be with the Note field; if I change Note to a different field in the tblSubmittalNotes the sql works just fine.

Hi. Just curious, why are you "duplicating" the Notes in the same table? What error message were you getting with the above SQL statement?

 

[andy1968]

Long Text.


Yes, duplicating into the same table.

 

[theDBguy]

Long Text.

Yes, duplicating into the same table.

Hi. Sorry, I noticed that when I reread your post, so I updated my post and asked a different question.

 

[andy1968]

Error 3134 (Syntax error in INSERT INTO statement.) in procedure



I'm adding a new parent record, and want to duplicate the notes to the new parent.

 

[theDBguy]

Error 3134 (Syntax error in INSERT INTO statement.) in procedure

I'm adding a new parent record, and want to duplicate the notes to the new parent.

Okay, try to do a Debug.Print and then copy and paste the resulting SQL statement in the Query Designer, so you can see where the problem is.

 

[andy1968]

Great.

Changed the sql as follow

strSql = "INSERT INTO [tblSubmittalNotes] ( SerialNumber, [Note]) " & _
                   "SELECT " & lngID & " As SerialNumber, Note " & _
                  "FROM [tblSubmittalNotes] WHERE SerialNumber = " & ingOldID & ";"

Working great.


Thanks for the tip.

 

[theDBguy]

Great.

Changed the sql as follow

strSql = "INSERT INTO [tblSubmittalNotes] ( SerialNumber, [Note]) " & _
                   "SELECT " & lngID & " As SerialNumber, Note " & _
                  "FROM [tblSubmittalNotes] WHERE SerialNumber = " & ingOldID & ";"

Working great.

Thanks for the tip.

Congratulations! Glad to hear you got it sorted out. Good luck with your project.