Run-time error '3075': Syntax error in (comma) (1 Viewer)

R

RickRo

Registered User.
Local time
Today, 13:10
Joined
Mar 16, 2013
Messages
18
Hello All,

I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...".
Here is the code:
Code: 
Private Sub XferDataToTempTable()
Dim db As Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "INSERT INTO tblTemp " _
    & "(ChangeType, ChangeDate, ImpactTicket, Status, IBMTag, " _
    & "SerialNumber, Class, Manufacturer, SubCatagory, Make, AssetComments, " _
    & "FromBuilding, FromFloor, FromRoom, FromHostname, ToBuilding, ToFloor, " _
    & "ToRoom, ToHostname) " _
    & "Select (tblAsset.ChangeType, tblAsset.ChangeDate, tblAsset.ImpactTicket, " _
    & "tblAsset.Status, tblAsset.IBMTag, tblAsset.SerialNumber, tblAsset.Class, " _
    & "tblAsset.Manufacturer, tblAsset.SubCatagory, tblAsset.Make, " _
    & "tblAsset.AssetComments, tblAsset.FromBuilding, tblAsset.FromFloor, " _
    & "tblAsset.FromRoom, tblAsset.FromHostname, tblAsset.ToBuilding, " _
    & "tblAsset.ToFloor, tblAsset.ToRoom, tblAsset.ToHostname) " _
    & "From [tblAsset] " _
    & "WHERE [tblAsset.SerialNumber]=[Forms]![frmChange2]![SerialNumber]"
 
Debug.Print strSQL
DoCmd.RunSQL strSQL
End Sub

The debug output is:
INSERT INTO tblTemp (ChangeType, ChangeDate, ImpactTicket, Status, IBMTag, SerialNumber, Class, Manufacturer, SubCatagory, Make, AssetComments, FromBuilding, FromFloor, FromRoom, FromHostname, ToBuilding, ToFloor, ToRoom, ToHostname) Select (tblAsset.ChangeType, tblAsset.ChangeDate, tblAsset.ImpactTicket, tblAsset.Status, tblAsset.IBMTag, tblAsset.SerialNumber, tblAsset.Class, tblAsset.Manufacturer, tblAsset.SubCatagory, tblAsset.Make, tblAsset.AssetComments, tblAsset.FromBuilding, tblAsset.FromFloor, tblAsset.FromRoom, tblAsset.FromHostname, tblAsset.ToBuilding, tblAsset.ToFloor, tblAsset.ToRoom, tblAsset.ToHostname) From [tblAsset] WHERE [tblAsset.SerialNumber]=[Forms]![frmChange2]![SerialNumber]

I tried this with just one data element, and it wrote to the table just fine:
Code: 
Private Sub XferDataToTempTable()
Dim db As Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "INSERT INTO [tblTemp]([ChangeType]) " _
    & "Select (ChangeType) " _
    & "From [tblAsset] " _
    & "WHERE [tblAsset.SerialNumber]=[Forms]![frmChange2]![SerialNumber]"
 
Debug.Print strSQL
DoCmd.RunSQL strSQL
End Sub

What am I missing?

Rick
 
J

JHB

Have been here a while
Local time
Today, 22:10
Joined
Jun 17, 2012
Messages
7,732
Try the below:
strSQL = "INSERT INTO tblTemp " _
& "(ChangeType, ChangeDate, ImpactTicket, Status, IBMTag, " _
& "SerialNumber, Class, Manufacturer, SubCatagory, Make, AssetComments, " _
& "FromBuilding, FromFloor, FromRoom, FromHostname, ToBuilding, ToFloor, " _
& "ToRoom, ToHostname) " _ & "Select (tblAsset.ChangeType, tblAsset.ChangeDate, tblAsset.ImpactTicket, " _
& "tblAsset.Status, tblAsset.IBMTag, tblAsset.SerialNumber, tblAsset.Class, " _
& "tblAsset.Manufacturer, tblAsset.SubCatagory, tblAsset.Make, " _
& "tblAsset.AssetComments, tblAsset.FromBuilding, tblAsset.FromFloor, " _ & "tblAsset.FromRoom, tblAsset.FromHostname, tblAsset.ToBuilding, " _
& "tblAsset.ToFloor, tblAsset.ToRoom, tblAsset.ToHostname) " _
& "From [tblAsset] " _
& "WHERE [tblAsset.SerialNumber]=" & "" & [Forms]![frmChange2]![SerialNumber] & ""
Click to expand...
 
R

RickRo

Registered User.
Local time
Today, 13:10
Joined
Mar 16, 2013
Messages
18
I tried that, and I have the same error. I looked at the changed code and it appears that the last line was changed
Code: 
 & "WHERE [tblAsset.SerialNumber]=" & "" & [Forms]![frmChange2]![SerialNumber] & ""
When I look at this in the immediate window I don't have anything after the equals sign.
Again, this works with just one field in the SQL, so I think the WHERE statement is correct.
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 22:10
Joined
Nov 3, 2010
Messages
6,142
so add 5 fields back to your sql. If you get the error then it's there! If not, then add another 5. Etc. This is in fact a standard method of debugging i-can't-see-it type of errors.

The other method, of posting it here, works if someone with too much time on their hands can be bothered :D
 
R

RickRo

Registered User.
Local time
Today, 13:10
Joined
Mar 16, 2013
Messages
18
Well thanks Spikepl, I have done that already; I got the error with all of the fields, so I backed it down to just one, and it worked. I add just one more, and I get the error.
Which is why I posted here - not to get someone with to much time on their hands, but perhaps find someone to point me in the right direction to troubleshoot.
 
J

JHB

Have been here a while
Local time
Today, 22:10
Joined
Jun 17, 2012
Messages
7,732
RickRo said:
Well thanks Spikepl, I have done that already; I got the error with all of the fields, so I backed it down to just one, and it worked. I add just one more, and I get the error.
Click to expand...
Is the field type equal in both table? And the value you try to add, does it be the right type for the "ChangeDate" field in the "tblTemp" table?
Put the query in the QBE - window, it is easier to debug from there.
 
Last edited:
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 16:10
Joined
Jan 23, 2006
Messages
15,379
INSERT INTO tblTemp (ChangeType, ChangeDate, ImpactTicket, Status, IBMTag, SerialNumber, Class, Manufacturer, SubCatagory, Make, AssetComments, FromBuilding, FromFloor, FromRoom, FromHostname, ToBuilding, ToFloor, ToRoom, ToHostname) Select (tblAsset.ChangeType, tblAsset.ChangeDate, tblAsset.ImpactTicket, tblAsset.Status, tblAsset.IBMTag, tblAsset.SerialNumber, tblAsset.Class, tblAsset.Manufacturer, tblAsset.SubCatagory, tblAsset.Make, tblAsset.AssetComments, tblAsset.FromBuilding, tblAsset.FromFloor, tblAsset.FromRoom, tblAsset.FromHostname, tblAsset.ToBuilding, tblAsset.ToFloor, tblAsset.ToRoom, tblAsset.ToHostname) From [tblAsset] WHERE [tblAsset.SerialNumber]=[Forms]![frmChange2]![SerialNumber]
Click to expand...

You don't need this brackets in red.

When you do a debug.print of your SQL, any form based data should be rendered to a value. If you are seeing the literal
[Forms]![frmChange2]![SerialNumber]
Click to expand...
, then your form based data has not been rendered. (Where rendered means evaluated and resolved as some number/string etc output)

The implication is you have a syntax error in how you have referenced the form based data.
 
R

RickRo

Registered User.
Local time
Today, 13:10
Joined
Mar 16, 2013
Messages
18
That was it - no parenthesis needed on the select portion. Thank you very much!
 
You must log in or register to reply here.

Similar threads

C
Run-time error 3075 (syntax error (comma) in query expression...)
Replies
8
Views
1,577
plog
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom