Inserting dates in Access using VBA

Question

I have a date field in my table, with "dd/mm/yyyy" format.

I am trying to insert into that field a variable through a form using VBA code. I have this:

vardtedate = CDate(Format(Me.dtedate.Value, "dd/mm/yyyy"))
DoCmd.RunSQL "INSERT INTO table (dtedate) VALUES (#" & vardtedate & "#);"

It works fine, but only when the day is over 12. If I try to insert something like '12/06/2016' it shows it reversed, like '06/12/2016', and the field takes that date as 6th of december instead of 12 of june. What am I doing wrong? What am I missing?

I tried to parametize and the problem persists.

Possible duplicate of Access VBA & SQL date formats . Don't mind that this question is about a SELECT statement, the problem is identical. — Andre
Parameterize the query and use Date types instead of Strings. — Comintern
Not sure if that works vardtedate = CDate(Format(Me.dtedate.Value, "mm/dd/yyyy")). Reverse month and day when you set vardtedate — Seb
@Andre I tried to parametize following the answers in there and I'm still having the same issue. — Iván Gómez Brea
Please edit your question and add the new code with parameter. — Andre

Iván Gómez Brea Iván Gómez Brea · Accepted Answer · 2016-11-25T16:48:00

So I was looking for solutions and I found this thread Inserting current DateTime into Audit table. Apparently when you try to insert a date value through a sql statement it converts ambiguous date formats to "mm/dd/yyyy". I formatted the variable to "yyyy/mm/dd" and now works perfectly.

vardtedate = CDate(Format(Me.dtedate.Value, "dd/mm/yyyy"))
DoCmd.RunSQL "INSERT INTO table (dtedate) VALUES (#" & Format(vardtedate, "yyyy-mm-dd") & "#);"

Inserting dates in Access using VBA

3 Answers