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
– Andre, Commented Nov 25, 2016 at 13:15
Parameterize the query and use Date types instead of Strings. — Comintern
– Comintern, Commented Nov 25, 2016 at 14:37
Not sure if that works vardtedate = CDate(Format(Me.dtedate.Value, "mm/dd/yyyy")). Reverse month and day when you set vardtedate — Seb
– Seb, Commented Nov 25, 2016 at 14:39
@Andre I tried to parametize following the answers in there and I'm still having the same issue. — Iván Gómez Brea
– Iván Gómez Brea, Commented Nov 25, 2016 at 15:25
Please edit your question and add the new code with parameter. — Andre
– Andre, Commented Nov 25, 2016 at 15:27

Community · Accepted Answer · 2017-05-23 11:53:11Z

2

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") & "#);"

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Nov 25, 2016 at 16:48

Iván Gómez Brea

311 gold badge1 silver badge4 bronze badges

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

Comments

user7075507 · Accepted Answer · 2016-11-27 14:51:30Z

0

Yes, it has to do with the regional settings your desktop is set to. United States data conventions, are totally different from European, or some other standard. See the link below for details.

https://support.office.com/en-us/article/Set-default-values-for-fields-or-controls-99508d03-b28b-4057-9652-dac1c4c60d86

As you found out, setting the format forces a fix.

answered Nov 27, 2016 at 14:51

user7075507

Comments

Mahdi Dhifi · Accepted Answer · 2019-11-27 17:51:03Z

0

CDate(Format(Date.Now, "MM/dd/yyyy"))

answered Nov 27, 2019 at 17:51

Mahdi Dhifi

1291 silver badge1 bronze badge

Collectives™ on Stack Overflow

Inserting dates in Access using VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related