Determine the Locale Aware Dates & Number Separators

Determine the characters used to separate date components, thousands, and the decimal and integer portion of numbers for the locale in effect. This function returns a string that you can use in the Format$() command to convert any valid date to display properly according to the regional settings in effect.

Download Source Code

Add this code to the Load event:

  Private Sub Form_Load()
    Dim sDateSep As String
    Dim sDatefmt As String 

    lblLocale(0) = Mid$(Format("1234", "Standard"), 6, 1)
    lblLocale(1) = Asc(lblLocale(0))
    lblLocale(2) = Mid$(Format("1234", "Standard"), 2, 1)
    lblLocale(3) = Asc(lblLocale(2))
    sDatefmt = fGetDateFormatString(sDateSep)
    lblLocale(4) = sDateSep
    lblLocale(5) = Asc(lblLocale(4))
    lblLocale(6) = sDatefmt
  End Sub

Add this code to the General section of the form:

Private Function fGetDateFormatString(cSeparator As String) As String
  Dim sLocaleDate As String
  Dim sWorkDate As String
  Dim sFormat As String
  Dim sTemp As String
  Dim iLoc As Integer
  Dim iLeft As Integer
  Dim iRight As Integer
  Dim iMid As Integer
  Dim iDD As Integer
  Dim iMM As Integer
  Dim iYY As Integer
  Dim iCC As Integer
  Dim iYYYY As Integer
  Dim sYYYY As String
  Const sKnownDate = "08/25/58"
  '
  ' Convert a known date to the regional format
  '
  sLocaleDate = Format(sKnownDate, "Short Date")
  sWorkDate = sLocaleDate
  '
  ' Determine the separator character
  '
  For iLoc = 1 To 5
    cSeparator = Mid(sLocaleDate, iLoc, 1)
    If Not IsNumeric(cSeparator) Then Exit For
  Next
  '
  ' Parse the date into its components
  '
  iLoc = InStr(1, sWorkDate, cSeparator)
  iLeft = Val(Left$(sLocaleDate, iLoc - 1))
  sWorkDate = Mid$(sWorkDate, iLoc + 1)
  iLoc = InStr(1, sWorkDate, cSeparator)
  '
  ' Correct regional date format 4/98
  '
  sTemp = Mid$(sWorkDate, 1, iLoc - 1)
  If IsNumeric(sTemp) Then
    iMid = Val(Mid$(sWorkDate, 1, iLoc - 1))
  Else
    iMid = Month(sLocaleDate)
  End If
  
  sWorkDate = Mid$(sWorkDate, iLoc + 1)
  iRight = Val(Mid$(sWorkDate, 1))
  '
  ' Locale aware functions
  '
  iDD = Day(sLocaleDate)
  iMM = Month(sLocaleDate)
  iYYYY = Year(sLocaleDate)
  sYYYY = CStr(iYYYY)
  iCC = Val(Left$(sYYYY, 2))
  iYY = Val(Right(sYYYY, 2))
  '
  ' Is the left component the day, month or year??
  '
  Select Case iLeft
    Case iDD
       sFormat = "dd/"
    Case iMM
       sFormat = "mm/"
   Case iYY, iYYYY
       sFormat = "yyyy/"
  End Select
  '
  ' How about the middle?
  '
  Select Case iMid
    Case iDD
       sFormat = sFormat & "dd/"
    Case iMM
       sFormat = sFormat & "mm/"
    Case iYY, iYYYY
       sFormat = sFormat & "yyyy/"
  End Select
  '
  ' And the right component is:
  '
  Select Case iRight
    Case iDD
       sFormat = sFormat & "dd"
    Case iMM
       sFormat = sFormat & "mm"
    Case iYY, iYYYY
       sFormat = sFormat & "yyyy"
  End Select

  fGetDateFormatString = sFormat
End Function
  

The return value of this function can be used in the Format$() command to convert a date to the regional settings in effect. For example:

  sFormatString =  fGetDateFormatString(sSeparator)
  sNewDate = Format$(sOldDate, sFormatString)

If sOldDate is "12/31/1998" and the regional settings are German, sNewDate will be "31.12.1998"

Download the source code Open the Regional Settings applet in Control Panel Select a country from the drop down on the Regional Settings tab and apply the change Run the project.