Date input regardless of international settings 1

[xlemosx]

Hi,

I'm writing a VBA access program that is to be used around the the department i work in. Now you would think being in Canada and only being for one department everyone would be using the Candian regional settings, but this is not the case. Some computers are using US and some Candian meaning the date varies by computer with mm/dd/yyyy and dd/mm/yyyy.

In my VBA i have an ADO object that doing a Query based on the date entered and if i understand correctly Access (2003)queries force the US date/time format. Now while i can easily write code to change my format to mm/dd/yyyy by picking apart my Candian date and switching it the month and day i run into a problem when someone else is using a machine with the US date format.

my Program does not check to see what the users regional settings are it just does the switch so someone using dd/mm/yyyy gets mm/dd/yyyy and someone using mm/dd/yyyy would get dd/mm/yyyy. So basically this fixes the problem with Candian regional settings but creates a problem with US regional settings.

So basically my question is can i check a users regional settings before doing this switch operation in an IF kind of like:

IF (regional setting = Canada) Then
change date format to US
Else IF (Regional Setting = US) Then
do nothing
Else
your SOL change you regional settings
End IF

Any opinions? or Suggestions?

~Thanks

 

[MajP]

No, you definitely cannot do that.
Behind the scenes all dates are stored in the same manner. What you see is only a formatting. Dates are stored as elapsed days since 30Dec 1899 and a decimal portion holding the fraction of the next day.

To do this you convert your sql string. See the following.

http://allenbrowne.com/ser-36.html

 

[SkipVought]

The simplest way is to use a yyyy/mm/dd unambiguous structure.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlemosx]

thanks MajP, the info on that website worked great. I looked through that website before when i was searching google but i didn't read it properly and thought it didn't apply to my case :S lol

I now have:

"SELECT TimeEntryAppName.App AS AppName, Sum(Extracts3.Hours) AS SumOfHours " & _
            "FROM TimeEntryAppName INNER JOIN Extracts3 ON TimeEntryAppName.TimeEntry = Extracts3.[Time Entry Code] " & _
            "OR TimeEntryAppName.TimeEntry = Extracts3.[Comment] " & _
            "Where WorkDate BETWEEN " & Format(stSelectedDate, "\#mm\/dd\/yyyy\#") & " AND " & Format(stQuarterStart, "\#mm\/dd\/yyyy\#") & " " & _
            "GROUP BY TimeEntryAppName.App;"

and i have tested it on Canadian and US settings.

 

[fumei]

The simplest way is to use a yyyy/mm/dd unambiguous structure. "

Skip, Skip, that would be too easy.

Until we have a true One World Government, this is going to be an issue.

Just kidding...about the OWG. Ain't gonna happen.

I sympathize with xlemosx, I too am in Canada, and I too have to deal with inconsistent date formats, even in the same department.


unknown

 

[strongm]

>>can i check a users regional settings before doing this switch operation in an IF kind of like:
>No, you definitely cannot do that.

Yes, you definitely can. I wouldn't advise it though. Really.

 

[macropod]

An even better way is you have dropdowns for selecting the year, month and day, so only valid data can be input. Even with code that does regionalisation checking, it's all too possible for people to misunderstand the input requirements for a given PC when:

Some computers are using US and some Candian

Cheers
[MS MVP - Word]