Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    44

    Unanswered: How to insert a Date in SQL INSERT statement

    How would you pass a Date to a Date/Time column in Access?

    I know with Text you do: ' " + string + " '

    I know with numbers you do: " + number + "

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    #" + string + "#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    44
    I got it to take the SQL statement, but it returns with a data type mis match error? My Code is below. The Res_Date is set as a date/time in the access db.

    Private Sub Command0_Click()
    Dim database1 As database 'db declared as a database object.
    Dim NumberOfDays As Integer
    NumberOfDays = 10

    Set database1 = CurrentDb 'sets the database to the current db
    Dim daycounter As Integer
    For daycounter = 0 To NumberOfDays
    Dim LDate As Date
    LDate = DateAdd("d", daycounter, #4/1/2010#) ' Enter date as month/day/Year
    Dim SQLINSERT As String
    SQLINSERT = "INSERT INTO TestDate (Res_Date) VALUES (#" + LDate + "#)"
    MsgBox (SQLINSERT)
    database1.Execute SQLINSERT
    Next daycounter
    MsgBox ("done")


    End Sub
    Last edited by tvb2727; 03-30-10 at 22:56.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Never, ever use the plus character as a string concatenation operator !

    Your code should be:
    Code:
    SQLINSERT = "INSERT INTO TestDate (Res_Date) VALUES (#" & LDate & "#)"
    Also, it's better to fully qualify the database object when you declare it, because there are two libraries in Access that provide a DataBase object: DAO and ADODB. In the present case, you need a DAO Database object (because you will assign CurrentDb to it), so your code should be:
    Code:
    Dim database1 As DAO.Database 'db declared as a database object
    .
    Have a nice day!

  5. #5
    Join Date
    Jan 2010
    Posts
    44
    Thanks you very much!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Sinndho View Post
    Never, ever use the plus character as a string concatenation operator !
    I disagree - but you must be aware that the plus and the ampersand behave differently.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •