Script to Change Format

APD Toronto
APD Toronto used Ask the Experts™
on
Hi Experts,

Is it possible to write a VBA Code to check if the system's Short Date format is mm/dd/yyyy, and change it if isnt?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Just a comment - as opposed to a solution.
Seems doing so could really piss off a user(s) who wants that Format.

Instead, why not hard code the format you want to display in a Control's Format property and/or in code.  This will override the Windows whatever format.

This is what I do ... as I prefer mm-dd-yyyy

mx
APD TorontoSoftware Developer

Author

Commented:
pissing off the user would be a bonus :)

the application is huge, and dates are used in literaly 100s of places, so my solution above is most economical.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<the application is huge, and dates are used in literaly 100s of places, so my solution above is most economical. >
By "100s of places", ...do you mean geographic locations?
(different countries)
Because changing this for all computers in all countries may cause problems...
There may be some utilities installed on some machines that need a different date setting...

Is there a specific issue you are having...?
Like MX, I am just a bit wary of just blowing though and making this change for all computers...
...When a change to the date format in Access may be all that is really needed...


In any event, a link to investigate:
http://en.allexperts.com/q/Excel-1059/2010/9/change-Regional-Setting-short.htm

JeffCoachman
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

APD TorontoSoftware Developer

Author

Commented:
Because changing this for all computers in all countries may cause problems...

WOW.  It's one office, 300 sqft with 7-10 PCs.  

What i meant by 100 places was, something like 30 forms + 50 sub routines + 20 queries = 100 places
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Just curious ... does the Format really matter since it has not bearing on how the date is actually stored in a table internally ?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
MX,

I'll leave this to you...
I gotta get started on the Holiday stuff...
;-)

Jeff
Most Valuable Expert 2012
Top Expert 2014
Commented:
Hi, I guess it's up to you whether you want to make your system more robust, or just force the change of the "short date format" by using this code:

Set objShell = CreateObject("WScript.Shell")
sDateFormat = objShell.RegRead("HKCU\Control Panel\International\sShortDate")
If sDateFormat <> "mm/dd/yyyy" Then objShell.RegWrite "HKCU\Control Panel\International\sShortDate", "mm/dd/yyyy", "REG_SZ"

Open in new window


Regards,

Rob.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You have to deal with possible different Windows settings for the users, as in a properly set up environment, users will not have been granted permission to change the date and time settings.

The method is simple; whenever a date/time is displayed, set the Format property of the textbox to: mm/dd/yyyy

/gustav

Commented:
Private Const LOCALE_SSHORTDATE = &H1F
Private Const WM_SETTINGCHANGE = &H1A
     
Private Const HWND_BROADCAST = &HFFFF&

Private Declare Function SetLocaleInfo Lib "kernel32" Alias _
         "SetLocaleInfoA" ( _
            ByVal Locale As Long, _
            ByVal LCType As Long, _
            ByVal lpLCData As String) As Boolean
     
Private Declare Function PostMessage Lib "user32" Alias _
         "PostMessageA" ( _
          ByVal hwnd As Long, _
          ByVal wMsg As Long, _
          ByVal wParam As Long, _
          ByVal lParam As Long) As Long
   
Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long


Sub chgDate()
Dim dwLCID As Long
Dim myDate As String
       
dwLCID = GetSystemDefaultLCID()
       
       
If SetLocaleInfo(dwLCID, LOCALE_SSHORTDATE, "dd/MM/yyyy") = False Then
     MsgBox "Failed"
     Exit Sub
End If
PostMessage HWND_BROADCAST, WM_SETTINGCHANGE, 0, 0
       
End Sub
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"as in a properly set up environment, users will not have been granted permission to change the date and time settings."
Especially at our LARGE company now that Win7 is being deployed ... total lock down ... barely can write to Registry at all.

mx
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yeah, same goes for changing screen resolution - popular in the days of CRT monitors - a big no-no.

/gustav
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well .. users here can manually change screen res of course ... many still use 800x600 on 24" dual monitors!  Can you say"  I cant' see' !
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Really? I haven't seen that on modern flat screen monitors. We always use the default (optimum) resolution.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial