UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need To Determine Window's Date Format - Possible?, Access 2007    
 
   
Retired
post Oct 30 2017, 12:56 AM
Post#1



Posts: 437
Joined: 3-March 07
From: Near Dallas, TX USA


• My application setup requires the user to select the application’s default date format (USA or Euro or International). Occasionally, they leave the default USA format when they’re in a different country. This causes trouble.
• If the application’s date format selected does not agree with Window’s date format some reports will not run correctly.
So, is it possible to determine the computer’s date format setting? Using VBA?
• Using Access 2007.
Go to the top of the page
 
moke123
post Oct 30 2017, 04:59 AM
Post#2



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



have you read this?
AB International Dates
Go to the top of the page
 
BruceM
post Oct 30 2017, 06:28 AM
Post#3


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I think part of moke's point is that you should not rely on users setting the date format in any particular way, but rather you should format the dates as needed for the application. Dates are not stored with a format. Changing the format does not change how date/time values are stored, no matter if the change is in the Windows regional settings, the table field, or anywhere else it is displayed. If a format is not specified, the user-selected format will be applied.
Go to the top of the page
 
Retired
post Oct 30 2017, 12:43 PM
Post#4



Posts: 437
Joined: 3-March 07
From: Near Dallas, TX USA


Moke123,

Yes, I have. From what I remember it talks of formatting dates not defining them as m/d/y or d/m/y, etc.
For instance, it doesn't seem possible to determine whether dates such as 1/1/17 is m/d/y or d/m/y. I would like to know.

Thanks, JW
Go to the top of the page
 
BruceM
post Oct 30 2017, 12:50 PM
Post#5


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I don't understand the question. 1/1/17 can only be one date. However, any date can be formatted to show the month spelled out or abbreviated.

Format(DateValue,"dd-mmm-yyyy")
Format(DateValue,"mmmm dd\, yyyy")

It may help if you describe the specific practical problem you are trying to solve.
Go to the top of the page
 
theDBguy
post Oct 30 2017, 01:02 PM
Post#6


Access Wiki and Forums Moderator
Posts: 71,229
Joined: 19-June 07
From: SunnySandyEggo


Hi JW,

See if any of the information in this other discussion helps.

Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Retired
post Oct 30 2017, 01:52 PM
Post#7



Posts: 437
Joined: 3-March 07
From: Near Dallas, TX USA


Bruce,

I'm aware that dates are stored as numbers and the format the dates that appear in forms and reports is the same format as selected in Windows.

If the app was just started, handling intn'l date's would have perhaps been handled differently. Unfortunately, it's too late to rethink this. The app is 8 yrs old and has now 200 forms, 130 rpts and 11 modules. The difficulty experienced is when a date is entered using a different format than what their computer is set to.

Incidentally, during setup, all the user does is click one of three option buttons to specify the format native to his country - and then all is well.

Thanks for your comment,
JW
Go to the top of the page
 
BruceM
post Oct 30 2017, 02:39 PM
Post#8


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


OK, now I understand that you meant the user selects a date format in the application, not that they are expected to change their regional settings. I would have expected that without a format specified, or with Short Date specified (or Long Date, but that is not often a choice for data entry) a date/time value entered in the user's regional settings format would be recorded correctly. For instance, 11/2/17 would be recorded as November 2 if the regional format is mm/dd/yyyy, and February 11 if dd/mm/yyyy.

If it is about the user making a choice before beginning, could you use a startup form that compels a selection before seeing the data entry form? If so, perhaps the selection could be stored so as to be associated with the user.

I have seen Application.International as a suggested way to get the date format, but in Excel only, not Access. Maybe it is in Word or something else in Office, but the point is there does not seem to be an Access equivalent. I have not found a way to make it available to Access.
Go to the top of the page
 
PhilS
post Oct 30 2017, 05:17 PM
Post#9



Posts: 404
Joined: 26-May 15
From: The middle of Germany


QUOTE
So, is it possible to determine the computer’s date format setting? Using VBA?

Yes, I hacked together a small sample module: http://codekabinett.com/page.php?Theme=10&...-VBA-api-sample

--------------------
Go to the top of the page
 
Retired
post Nov 2 2017, 05:12 PM
Post#10



Posts: 437
Joined: 3-March 07
From: Near Dallas, TX USA


BruceM,

Your idea about compelling the user to choose his format before continuing the setup ... is compelling. I may try it.

Tanks, JW
Go to the top of the page
 
Retired
post Nov 2 2017, 05:14 PM
Post#11



Posts: 437
Joined: 3-March 07
From: Near Dallas, TX USA


Phil,
Thanks for the module code. I'm hopeful and will try it.
JW
Go to the top of the page
 
ADezii
post Nov 2 2017, 05:47 PM
Post#12



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. You can retrieve the Short and Long Date Formats in the Regional Settings with the GetLocalInfo() API Call and a couple of Constants.
  2. API and Constant Declarations in a Standard Code Module:
    CODE
    Public Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
                                               ByVal lpLCData As String, ByVal cchData As Long) As Long
    Public Const LOCALE_USER_DEFAULT = &H400
    Public Const LOCALE_SSHORTDATE = &H1F       ' Short Date Format string
    Public Const LOCALE_SLONGDATE = &H20        ' Long Date Format string
  3. Main Code Segment:
    CODE
    Dim strLocale As String
    Dim lngRet As Long
    Dim strMsg As String
        
    'Get Short Date Format
    strLocale = Space(255)
    lngRet = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, strLocale, Len(strLocale))
    strLocale = Left(strLocale, lngRet - 1)
        
    strMsg = "Short Date Format: " & strLocale
        
    'Get Long Date Format
    strLocale = Space(255)
    lngRet = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SLONGDATE, strLocale, Len(strLocale))
    strLocale = Left(strLocale, lngRet - 1)
        
    strMsg = strMsg & vbCrLf & vbCrLf & "Long Date Format: " & strLocale
        
    Debug.Print strMsg
  4. OUTPUT based on my Personal PC:
    CODE
    Short Date Format: M/d/yyyy

    Long Date Format: dddd, MMMM d, yyyy
Go to the top of the page
 
JonSmith
post Nov 3 2017, 03:51 AM
Post#13



Posts: 3,161
Joined: 19-October 10



In the interest of completeness, you can also read all this stuff from the registry and even change it (not recommended!!).


I ended up using in Excel where the users had inconsistent list separators which are used to separate formulas. 90% of the time VBA was happy using a default comma as list separator but under really specific conditions I believe to do with structured references you HAVE to use the local separator. I fixed by reading the registry and returning whatever value was there. You could do the same and check each time the db is opened and update the date setting accordingly.
Go to the top of the page
 
PhilS
post Nov 3 2017, 06:27 AM
Post#14



Posts: 404
Joined: 26-May 15
From: The middle of Germany


@ADezii: Well done! Much simpler solution to this particular problem then my module.

@JonSmith:
QUOTE
where the users had inconsistent list separators which are used to separate formulas.

Just as an additional reference: Daniel Pineault recently published an extensive article on retrieving the list separator on his blog.

--------------------
Go to the top of the page
 
ADezii
post Nov 3 2017, 06:41 AM
Post#15



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Thanks PhilS.
Go to the top of the page
 
Retired
post Nov 10 2017, 02:00 AM
Post#16



Posts: 437
Joined: 3-March 07
From: Near Dallas, TX USA


ADezil,

This worked so well! And the first time (made no typos). I'm so appreciative ... I'd offer you a beer cheers.gif but there's only one bottle left in the fridge frown.gif

Seriously, thanks. JW
Go to the top of the page
 
ADezii
post Nov 10 2017, 08:07 AM
Post#17



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


I'll take a rain check on the beer! guiness.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 09:02 AM