CODEWITHSUNDEEP
sqlvbadatems-accessformat
Iván Gómez Brea
Iván Gómez Brea

Reputation: 31

Inserting dates in Access using VBA

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.

Upvotes: 1

Views: 24848

Answers (3)

Mahdi Dhifi
Mahdi Dhifi

Reputation: 129

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

Upvotes: 0

user7075507
user7075507

Reputation:

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.

Upvotes: 0

Iván Gómez Brea
Iván Gómez Brea

Reputation: 31

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

Upvotes: 2

Related Questions