Date format with VBA - English VS French difference

Question

I have a problem with Access 2010. I'm using the included datepicker with the default textbox.

Where i work, there are computers that are in US date format (mm/dd/yyyy), and others in Canada date format (dd/mm/yyyy)

I cannot change the regional option of those computers.

I have succeded to force the date with format(date,"dd/mm/yyyy")

But when i chose a date with the default datepicker, it goes with the windows regional option, in this case, (mm/dd/yyyy) but in other computer, it will be ok, in (dd/mm/yyyy). I need to overide the dateformat with the datepicker but i don't know how.

This is a screenshot, the dateformat is in US format, and it's not okay.

Screenshot

Okay here we have a french today button on US English picker with presumably an mm/dd/yyyy format. Got to ask yourself what you are trying to achieve here... Don't mix locale's and don't fight them, especially mm/dd vs dd/mm as it will be wrong most of the time. I'm english, my pc is set up for english, I don't want anything to do with mm/dd/yyyy, I presume my colonial colleagues have similar feelings. So why do you want to force to dd/mm/yyyy, deal with dates as dates not strings. — Tony Hopkinson
I live in Canada, Quebec, where we all talk french at work. Only windows are sometime setup in english. But all users work in french, some of them are old and are confuse by the change in format date and this will lead to errors.¨ And the french button was there from the begining — Insecurefarm
It's easier to set the pcs up properly, than to try what you are doing, everytime you sniff near this stuff it will go wrong. — Tony Hopkinson

Jean-François Corbett Jean-François Corbett · Accepted Answer · 2011-12-10T20:00:57

I'm sure there's a more elegant way to do this, but I guess you could detect the locale-determined date format with something like this:

If CInt(Left(CStr(DateSerial(2012, 1, 2)), 2)) = 2 Then
    MsgBox "Canadian locale, date format dd/mm/yyyy."
Else
    MsgBox "US locale, date format mm/dd/yyyy."
End If

Then based on this, interpret the date-picker-chosen date and re-display it in your format of choice.

Date format with VBA - English VS French difference

2 Answers