Re: sql syntax error

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 10/11/04 

Date: Mon, 11 Oct 2004 14:43:33 -0400

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.) 

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"JohnE" <JohnE@discussions.microsoft.com> wrote in message
news:BC6BEC24-AAA3-4469-854E-AD90C4683D63@microsoft.com...
> 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

Relevant Pages

  • Re: Import date/time into Access
    ... Doug Steele, Microsoft Access MVP ... (no private e-mails, please) ... in a query do something like this: ... or must they be broken into a date field and a time field? ...
    (microsoft.public.access.externaldata)
  • RE: Convert GPS Date and Time
    ... Jerry Whittle, Microsoft Access MVP ... "Scott" wrote: ... display it in a query in two fields, a date field and a time field. ...
    (microsoft.public.access.queries)