Results 1 to 6 of 6
  1. #1
    tuna is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2010
    Posts
    21

    Question SQL INSERT INTO Date Syntax Error

    I'm trying to run the following SQL (PropertyID is Double, TimeStamp is Date/Time) but the TimeStamp part is causing a syntax error (Runtime error 3134) - am I missing something?



    Code:
    DoCmd.RunSQL "INSERT INTO tblProperties (PropertyID, TimeStamp) VALUES (1, #07/08/2010 06:37:43#)"
    I've also tried with quotes:
    Code:
    DoCmd.RunSQL "INSERT INTO tblProperties (PropertyID, TimeStamp) VALUES (1, #'07/08/2010 06:37:43'#)"
    Ideally, I would like to have somthing like Now() or dteDateVar in place of the static time in the end - I was just testing with the static date but failed at the first hurdle!

    Thanks very much

    Edit: Also, adding a semicolon at the end doesn't help

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I have a table Member. I am using this Insert query to enter names. The time Stamp Field property is set to Long Date.

    I have assigned a Variable dtDate =Now()


    Private Sub Command6_Click()
    On Error GoTo Err_Command6_Click
    Dim strSQL As String
    Dim dtDate As Date
    dtDate = Now()
    strSQL = "Insert into tblmember(MemberName,TimeStamp) Values ('Sam', #" & dtDate & "#)"
    CurrentDb.Execute strSQL, dbFailOnError


    Exit_Command6_Click:
    Exit Sub

    Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click

    End Sub


    if the Timestamp Field is a Text Field then # has to be replace with '

    strSQL = "Insert into tblmember(MemberName,TimeStamp) Values ('Sam', '" & dtDate & "')"

  3. #3
    tuna is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2010
    Posts
    21
    Thanks for your reply. As a test I've copied and pasted your code into my DB and simply changed the field names the "sam" value and ran it. I still get a syntax error. I've checked that my TimeStamp field has a data type of Date/Time. Any other suggestions? Thanks

  4. #4
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    735
    Hi Tuna,

    isn't Timestamp a reserved word? Try altering the name of the field to something like fldTimestamp.

    grNG

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    yes its a reserved word. try chaniging the field name.

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Quote Originally Posted by NoellaG View Post
    Hi Tuna,

    isn't Timestamp a reserved word? Try altering the name of the field to something like fldTimestamp.

    grNG
    Thanks NoellaG. Guess I am getting old now. LOL

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Syntax error
    By smikkelsen in forum Access
    Replies: 6
    Last Post: 04-28-2010, 09:38 AM
  2. Syntax Error
    By KLynch0803 in forum Programming
    Replies: 11
    Last Post: 02-04-2010, 01:45 AM
  3. sql syntax error
    By SMAlvarez in forum Access
    Replies: 1
    Last Post: 03-12-2009, 09:43 PM
  4. Hide zero value in report and date syntax
    By Alex Motilal in forum Access
    Replies: 0
    Last Post: 11-16-2008, 09:11 AM
  5. Syntax Error In Insert Into Statement
    By frankvfox in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums