SQL Help

[Starman]

Can someone give me a hand with a SQL expression. I've been staring at this thing for hours and can't figure out why I keep getting a syntax error.

strSQL = "INSERT INTO tblIncidentReport(OfficerID,IncidentReportNumber,IncidentReportLocation) " & _
        "SELECT " & [cboOfficer] & ", " & [txtReportNumber] & ", " & strSaveLoc
DoCmd.RunSQL strSQL

I'm sure it's something really simple, but I just can't seem to figure it out.

 

[georgedwilkinson]

Please replace this:

DoCmd.RunSQL strSQL

With this:

Debug.Print strSQL
DoCmd.RunSQL strSQL

and check the immediate window. If you still can't figure it out after that, post the results of the immediate window here.

Hint: you don't need the select statement. You would normally replace that with:
"VALUES ('" & [cboOfficer] & "', "' & [txtReportNumber] & "', '" & strSaveLoc & "')"

 

[MSAccessRookie]

Can someone give me a hand with a SQL expression. I've been staring at this thing for hours and can't figure out why I keep getting a syntax error.

strSQL = "INSERT INTO tblIncidentReport(OfficerID,IncidentReportNumber,IncidentReportLocation) " & _
        "SELECT " & [cboOfficer] & ", " & [txtReportNumber] & ", " & strSaveLoc
DoCmd.RunSQL strSQL

I'm sure it's something really simple, but I just can't seem to figure it out.

I would look at the types of the data being used. Officer and strSaveLoc sound like they would be strings, but txtReportNumber sounds like a converted number. If it is not being properly converted to a string, you can get an error.

 

[Starman]

Coding should definitely not be done on 2 hours sleep. I did fix the txtReportNumber because it was supposed to be a number and I changed SELECT into VALUES.

Here is the SQL string now...

strSQL = "INSERT INTO tblIncidentReport(OfficerID,IncidentReportNumber,IncidentReportLocation) " & _
            "VALUES (" & [cboOfficer] & ", " & [intReportNumber] & ", " & strSaveLoc & ")"

And here is the Debug.Print info...

INSERT INTO tblIncidentReport(OfficerID,IncidentReportNumber,IncidentReportLocation) VALUES (7, 100001, C:\Users\Valued Customer\Documents\Work\Public Safety\CC2\Templates\100001.doc)

 

[pbaldy]

Try

"VALUES (" & [cboOfficer] & ", " & [intReportNumber] & ", '" & strSaveLoc & "')"

 

[Starman]

That did it, pbaldy. Thanks all for your help.