VBA SQL error ... Need second set of Eyes

[Ziggy1]

Error: Syntax error in INSERT INTO statement


I have this VBA to create an SQL string, it worked but I added a new field to my table but I can't get it to load. The new Field is: option it is a Text data type in the table.

   strSQL = "insert into tblUpdateLog(user,starttime,endtime,duration,option)"
   strSQL = strSQL + "Values(" & "'" & GetUserName & "'" & "," & "#" & stTime & "#" & "," & "#" & EnTime & "#" & "," & Dur & ",'TEST'" & ")"

this is the output in the Immediate window.....

insert into tblUpdateLog(user,starttime,endtime,duration,option)Values('ziggy',#07/01/2010 9:31:27 AM#,#07/01/2010 9:31:27 AM#,0,'TEST')


Thanks

 

[DCrake]

& Dur & "[B][COLOR="Red"],[/COLOR][/B]'TEST'" & ")"

Your comma is in the wrong place

 

[Ziggy1]

Thanks DCrake,

But where should it go? it looks right to me... I tried putting it on it's ow with

Dur & "," & "'TEST'" & ")"

But I need a comma, there are 5 fields and I have 5 Values all with the correct data types, I even tried using a Variable to pass to the last field?

 

[DCrake]

strSQL = "insert into tblUpdateLog(user,starttime,endtime,duration,option)"
strSQL = strSQL + "Values(" & "'" & GetUserName & "'" & "," & "#" & stTime & "#" & "," & "#" & EnTime & "#" & "," & Dur & ",'TEST'" & ")"

For a start don't use a field named User this is an Access reserved word.

Values("'" & GetUserName & "', #" & stTime & "#, #" & EnTime & "#, " & Dur & ", 'TEST')"

Untested but try this

or this

sql = ""
sql = Sql & "INSERT INTO tblUpdateLog( User, StartTime, EndTime, Duration, Option) "
sql = sql & "SELECT '"  & GetUserName & "' AS A, "
sql = sql & "#" & sttime & "# AS B, "
sql = sql & "#" & EnTime & "# AS C, "
sql = sql & 100 & " AS D, " 
sql = sql & 'Test'  & " AS E;"

David

 

[Ziggy1]

This Line went RED so I changed to double quotes, but still didn't work?

SQL = SQL & "Test" & " AS E;"

Output:

INSERT INTO tblUpdateLog( xUser, StartTime, EndTime, Duration, Option) SELECT 'ziggy' AS A, #07/01/2010 10:31:40 AM# AS B, #07/01/2010 10:31:40 AM# AS C, 100 AS D, Test AS E;

 

[Ziggy1]

option is also a reserved word ( haven't looked it up), I changed them both and aam not getting an error now, but I need to double check the eyntax.... jusy give me a minute

 

[Ziggy1]

Ok I got it, this one is using a variable, but you certainly helped remind me not to use Reserved words, I'm normally good at not using things like NAME DATE but these 2 were less obvious.

   strSQL = "insert into tblUpdateLog(xuser,starttime,endtime,duration,xOption)"
   strSQL = strSQL + "Values(" & "'" & GetUserName & "'" & "," & "#" & stTime & "#" & "," & "#" & EnTime & "#" & "," & Dur & ",'" & MacName & "')"

thanks

 

[MSAccessRookie]

Ok I got it, this one is using a variable, but you certainly helped remind me not to use Reserved words, I'm normally good at not using things like NAME DATE but these 2 were less obvious.

   strSQL = "insert into tblUpdateLog(xuser,starttime,endtime,duration,xOption)"
   strSQL = strSQL + "Values(" & "'" & GetUserName & "'" & "," & "#" & stTime & "#" & "," & "#" & EnTime & "#" & "," & Dur & ",'" & MacName & "')"

thanks

I am not sure how this works, since it appears to be missing a space between ",xOption)" and "Values(".