Passing vba variable date to sql statement

Question

As a newbie in vba-sql, I am trying to populate a table using a for loop in vba and the Docmd.runsql to populate the table. The code works fine but then the values in the table are: 12/30/1899 for all the records. I used a message box to check and dt works fine. It gives me the date in the loop and moves to the next but in the table it only gives me one date: 12/30/1899.

Function dateTblUpdate()

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE FROM [date-table]"
    Dim dt As Date



    For dt = #1/1/2010# To DateSerial(Year(Now), Month(Now), Day(Now))
       'MsgBox DateSerial(Year(Now), Month(Now), Day(Now))
       'MsgBox dt
        DoCmd.RunSQL "Insert into [date-table] (the_date) values(" & dt & ")"

   Next
   DoCmd.SetWarnings True
End Function

date-table current outcome

the_date
----------
12/30/1899
12/30/1899
12/30/1899
12/30/1899
12/30/1899
12/30/1899

But Expected outcome:

the_date
------------
01/01/2010
01/02/2010
01/03/2010
01/04/2010

My date column is of date type and I believe sql is thinking dt is not a date so it is not saving it and saving the default date.... Any ideas?

HansUp · Accepted Answer · 2015-05-26 15:57:04Z

5

Include # characters before and after your literal date value. Otherwise the db engine will interpret something like 26/5/2015 as 26 divided by 5 divided by 2015 ... IOW as a math expression, not the date you intended.

"Insert into [date-table] (the_date) values(" & Format(dt, "\#yyyy-m-d\#") & ")"

However if you use a parameter query, you wouldn't need to bother about those # characters in the SQL statement:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim dt As Date
Dim strInsert As String

strInsert = "INSERT INTO [date-table] (the_date) VALUES (pDate)"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strInsert)

'For dt = #1/1/2010# To DateSerial(Year(Now), Month(Now), Day(Now))
For dt = #1/1/2010# To Date
    qdf.Parameters("pDate").Value = dt
    qdf.Execute dbFailOnError
Next

Note using the QueryDef.Execute method removes the motivation for DoCmd.SetWarnings False. Turning SetWarnings off suppresses information, including information which could be useful for troubleshooting.

edited May 26, 2015 at 15:57

answered May 26, 2015 at 15:44

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Coding Enthusiast Over a year ago

how did I know you will come up with a fancy answer. I always look forward to your answers. You are knowledgeable #respect

Mathieu Guindon Over a year ago

++ parameterized queries FTW

D Stanley · Accepted Answer · 2015-05-26 15:43:28Z

2

Since the SQL you're building is:

Insert into [date-table] (the_date) values(1/1/2010)

The interpreter may be doing integer division and saving a 0 value, which is the default date. Enclose the value in pound signs instead:

DoCmd.RunSQL "Insert into [date-table] (the_date) values(#" & dt & "#)"

answered May 26, 2015 at 15:43

D Stanley

153k12 gold badges190 silver badges257 bronze badges

1 Comment

Gustav Over a year ago

No, it will fail in a non-US environment. dt must be formatted to a string expression: Format(dt, "yyyy\/mm\dd").

Collectives™ on Stack Overflow

Passing vba variable date to sql statement

date-table current outcome

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

date-table current outcome

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related