Date Clash with Program and System

Changing the System Date Format is not really friendly to other applications. Undesired results can and will occur.

I have been down this road a couple of times, for dates and number formats. I do not have the code handy, but this the principle. You have to know that the internal format for dates is the US format "mm/dd/yyyy". You need to transform date strings to that format in order to convert them to dates.

When starting the application determine the date format. This does need some thinking as there multiple possibilities for the date format.
Try the following on a simple form with a label.
Label1.Caption = Format(Date, "mm/dd/yyyy")
Label1.Caption = Label1.Caption & vbNewLine & Format(Date, "Short Date")

The firstline will show the curent date in your desired format, the second will produce the System Date Format.

You will have to parse the standard formating produced by the first line. In your application you then have to work the other way around. [TIP:do not use the current date for that but Format(CVDate("05/31/2047"), "Short Date") You can then easily check at which position the day, month and year appear.]

Store the format found in a global variable gstrDateFormat.

Now when you have to convert a string value to a date use the following function:

Public Function gfstrToDate(strText As String) As Date
Select Case gstrDateFormat
Case "dd/mm/yyyy", "dd-mm-yyyy"
gfstrToDate = CVDate(Mid(strText, 4, 2) & "/" & Mid(strText, 1, 2) & "/" & Mid(strText, 7))
Case "mm/dd/yyyy"
gfstrToDate = CVDate(strText) 'already in the correct internal format
End Select
End Function

This does not cover all possible date formats, but that is upto you.

The internal format for dates is NOT half-backwards numeric but a number of seconds from some arbitrary time in the past. The external formatting of dates in Access does not compel any format other than what is set in Windows: short, long, whatever,they all get interpreted according to the local PC's setting. What you have done is use text or string where you should have used the type date/time. Change to that and your users will have no trouble: the same Access database can be accessed simultaneously by one person using natural dates, another using ascending and a third using half-backwards.

Agree with ROBO that to change the system date format isn't quite friendly to other programs. (However it's possible simply using API calls).

I have done it occasionally and hence the generic comment.

Anyway the steps:.
(1) Using API call, save the current format,
(2) Change to your format,
(3) On EOJ (End of Job), revert.

The problem however is that it gets applied to all programs running (minimised or likely to run soon). Imagine a scheduled virus check failing if it doesn't recognise that the date has elapsed.

Also, if the system ABENDS when your program is running (more possible in newly developed apps), the date is changed "permanently" and might go unnoticed.

So a solution might be to:
(1) Get and display the system format,
(2) Caution the user,
(3) Continue / Abort.

Another solution might be to get all dates in separate fields. I think most web sites do this.

Another way (if the user accepts) is to get the date in the descriptive format (Sun 16 March 2003 ...).

Internally, we can make our programs more rugged by doing date arithmetic using constructs like:

cstr(year()) & "/" & cstr(month()) & "/" & cstr(day())

or
year()*10000 +month()*100 + day()

and so on.

Hope this helps.

End of mail.

'Use the below function as
setDateFormat "dd/MM/yyyy"
'-------------------------
Public Function setDateFormat(Optional DtFormat As String)
On Error Resume Next
Dim dwLCID As Long

If IsMissing(DtFormat) Then
DtFormat = "dd/MM/yyyy"
End If

dwLCID = GetSystemDefaultLCID()
If SetLocaleInfo(dwLCID, LOCALE_SSHORTDATE, DtFormat) = False Then
MsgBox "Could not change the system date format."
Exit Function
End If
PostMessage HWND_BROADCAST, WM_SETTINGCHANGE, 0, 0
End Function
'-----------------------------------------
'Declarations.
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 Declare Function SetLocaleInfo Lib "kernel32" Alias "SetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Long
Public 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
Public Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long

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

This question was auto closed due to inactivity