Getting "Syntax error in INSERT INTO Statement" error

[mjschroed]

I have created the following piece of code to update a database (I am kind of new at this) but it fails with the above error. If I put the insert statement into a SQL window with the right values - it works fine. Here is the statement:

Dim UserProjName As String
Dim DRSiRecordType As Long
Dim DRSiResultNumber As Long
Dim DRSsDescription As String
Dim Year As Long
Dim Month As Long
Dim Amount As Long

Bunch of other stuff.....

sSql = "INSERT INTO Occupancy ( UserProjName, DRSiRecordType, DRSiResultNumber, DRSsDescription, Year, Month, Amount ) values (""" & UserProjName & """," & DRSiRecordType & "," & DRSiResultNumber & ",""" & DRSsDescription & """," & Year & "," & Month & "," & Amount & &quot"
l_cmdExecuteSQL.ActiveConnection = CurrentProject.Connection
l_cmdExecuteSQL.CommandText = sSql
l_cmdExecuteSQL.Execute

From the debugger window - it is building this query:

"INSERT INTO Occupancy ( UserProjName, DRSiRecordType, DRSiResultNumber, DRSsDescription, Year, Month, Amount ) values ("135",10000,6,"Mall Shop GLA Occ. Area SqFt",2002,1,286316)"

Thanks in advance,
Melissa

 

[zemp]

Your string values need to be surrounded by single quote (') not double quotes (&quot. Try this,

"INSERT INTO Occupancy ( UserProjName, DRSiRecordType, DRSiResultNumber, DRSsDescription, Year, Month, Amount ) values ('" & UserProjName & "'," & DRSiRecordType & "," & DRSiResultNumber & ",'" & DRSsDescription & "'," & Year & "," & Month & "," & Amount & &quot"


Thanks and Good Luck!

zemp

 

[mjschroed]

I just tried that but got the same error. Thanks for the suggestion though!

"INSERT INTO Occupancy ( UserProjName, DRSiRecordType, DRSiResultNumber, DRSsDescription, Year, Month, Amount ) values ('135',10000,6,'Mall Shop GLA Occ. Area SqFt',2002,1,286316)"

Melissa

 

[mjschroed]

I figured it out. Turns out Month and Year have system values (?) so they have to be enclosed in [].

This worked:

INSERT INTO Occupancy ( UserProjName, DRSiRecordType, DRSiResultNumber, DRSsDescription, [Year], [Month], Amount ) values ('" & UserProjName & "'," & DRSiRecordType & "," & DRSiResultNumber & ",'" & DRSsDescription & "'," & Year & "," & Month & "," & Amount & &quot

Thanks!

 

[LazyMe]

Yep, seems pretty logical, they're build in functions....


Greetings,
Rick

 

[zemp]

Yes, you have to watch out for Key words that the system wants to interpret as somrthing else. Good catch.

Thanks and Good Luck!

zemp