SQL Syntax Error

[damicomj]

Run-time errpr '3134':
Sytanx error in INSERT INTO statement.

I don't see what I am doing wrong. Thank you for helping in advance.

lblUsername.Caption = fOSUserName
 
strTable = "tblUserLog"
strColumns = "(Username, Time)"
strValues = "('" & lblUsername.Caption & "', '" & Time() & "')"
 
strSQL = "INSERT INTO " & strTable & " " & strColumns & " VALUES " & strValues & ";"
 
DoCmd.RunSQL strSQL
 

[julietbrown]

Try this ...

strSQL = "INSERT INTO tblUserLog (Username,Time) VALUES ('" & lblUsername.Caption & "'," & Time() & ")"

The messy stuff about single and double quotes and strings inside sql strings is a source of huge confusion to me, too! I think making more substrings has made your Insert statement even more difficult to unravel.

[Mariostg]

Time is a reserved word in access. It is a function. So if you use it as a field Name, you must enclose it between square brackets. [Time]
It would be much safer to use a different name to avoid confusion. LogTime... maybe.

[damicomj]

Juilet,
That didn't work either. There are 3 columns in the table: UserID, Username, Time. Does that change anything?

[damicomj]

I changed Time to Time1 and now I am getting:

Syntax error (missing operator) in query expression '1:31:20 PM'.

strSQL = "INSERT INTO tblUserLog (Username,Time1) VALUES (" & lblUsername.Caption & "," & Time() & ")"
 
DoCmd.RunSQL strSQL
 

[Mariostg]

Looks like you lost the single quotes surrounding the Time() function and your label caption.
You should have

strSQL = "INSERT INTO tblUserLog (Username,Time1) VALUES ('" & lblUsername.Caption & "','" & Time() & "')" 
 

[julietbrown]

The number of fields won't make any difference as long as the bit in brackets ... (Username,Time1) ... has the same number of fields in the same order as the stuff in the VALUES brackets, (and of course UserName and Time1 must be names of fields in the table)

If Time() is a date/time function you could try

strSQL = "INSERT INTO tblUserLog (Username,Time1) VALUES ('" & lblUsername.Caption & "',#" & Time() & "#)"

It's getting late and I'm well into my bottle of wine: I'll look in the morning and definitely sort this one out if someone else hasn't overnight.

Good luck!

[Oralloy]

You might try:

  Dim strSQL$
  strSQL = "INSERT " & vbCrLf & _
           "  INTO tblUserLog (Username,Time1)" & vbCrLf & _
           "  VALUES('" & lblUsername.Caption & "', " & _
                     "#" & CDate(Time()) & "#)"
 
DoCmd.RunSQL strSQL