sql syntax error

[Guest]

I am getting a syntax error from the following;
Dim strSQL As String
Dim MyTime
MyTime = Time
strSQL = "INSERT INTO
tblWhoLoggedOn(UserName,UserPassword,Date,Time) " & _
" VALUES (" & Me.cboUserName & "," & Me.txtUserPassword &
",Now(),MyTime)"
CurrentDb.Execute strSQL, dbFailOnError

I am wanting to track those who sign into the db. When the error comes up,
it highlites the last line (CurrentDb...).
Can anyone see the mistake made in this?
Thanks in advance to anyone who responds.
*** John

 

[Guest]

Try putting single quotes around the string values you want to insert. Also,
SQL won't know the Now() function or MyTime var. They should be single-quoted
as well. Try this:

strSQL = "INSERT INTO tblWhoLoggedOn(UserName,UserPassword,Date,Time)
VALUES ('" & Me.cboUserName & "','" & Me.txtUserPassword & "','" & Now()
"','" & MyTime & "')"

 

[Douglas J. Steele]

Values being inserted into text fields need to be delimited with quotes,
values being inserted into date/time fields need to be delimited with #.


Dim strSQL As String
Dim MyTime
MyTime = Time
strSQL = "INSERT INTO
tblWhoLoggedOn(UserName,UserPassword,LogonDate,LogonTime) " & _
" VALUES (" & Chr$(34) & Me.cboUserName & Chr$(34) & "," &
Chr$(34) & Me.txtUserPassword & Chr$(34) & ", #" & Format(Now(), "yyyy-mm-dd
hh:nn:ss") & "#, #" & Format(MyTime, "hh:nn:ss") & "#)"
CurrentDb.Execute strSQL, dbFailOnError

I don't understand why you've got a separate Time field. The Now function
provides you with both date and time.

Also, note that I renamed your date and time fields (to LogonDate and
LogonTime). Date and Time are reserved words in Access, and should not be
used for your own fields (nor variables nor form controls, etc.)

 

[Guest]

Doug is correct when he says to delimit date fields with #. I misread the
title of your post, and thought you were working with SQL Server when I said
to use single quotes for dates.