Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

date language in report

Posted on 2010-08-16
30
Medium Priority
?
1,772 Views
Last Modified: 2012-06-27
I need to set the date language in a field in a report. The language is not always the same as the system or database.

right now I use a format mmm\ dd\ yy.

How would I set the language to say English or French in a report?
0
Comment
Question by:Shawn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
  • 5
  • +1
30 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33451269
You can't do this easily.

First, the "Language" of the database is set in the Windows Control Panel, so you can't easilly change it on the fly.

You may also need one or more "Language Packs":
http://office.microsoft.com/en-us/language/

JeffCoachman


0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33451537
If your need are simple, you can try something like this:

;-)

JeffCoachman
Access-EEQ26406485ChangeReportLa.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33451538
need-->needs
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Shawn
ID: 33451770
i''ve opened the database but cannot see any difference between thsomething?e dates...sorry, am I missing
0
 
LVL 1

Author Comment

by:Shawn
ID: 33451773
i''ve opened the database but cannot see any difference between the dates...sorry, am I missing something?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33455107
There was no difference in the dates in my sample, perhaps I misread your post.
Did you see that the Captions changed in any event?
;-)

But the date can be re-formatted as well...
I think the French date Format is dd-mm-yyyy
(I used the International date format to avoid ambiguity)

Here is a new sample.
I am sure you can read through the code an make any needed adjustments...

;-)

Jeff
Access-EEQ26406485ChangeReportLa.mdb
0
 
LVL 1

Author Comment

by:Shawn
ID: 33455313
right, now I see the captions but it doesn't really help with my date in the report.

more specifically the report is an invoice. This invoice is sent to some people who read English, others who read French.

The only thing I have not managed to modify is the Date of the invoice and the due date.

eg in the invoice date field I have mmm\ dd\ yy which in English would give me say Febr 1 2010 and in French it would give févr 1 2010.

When my system is in French your English sample gives me the months in French. :(

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33455340
my sample is based on a system setting of "English.

All this means is that you must change the syntax to start from French..
Make sense...
0
 
LVL 1

Author Comment

by:Shawn
ID: 33455556
what I mean is I am looking to choose the language of the date...or at least the month. If English is selected in the database the month should be displayed in English regardless of the the system language. Unless I am missing something in your sample this is not the case.

To clarify further we have project managers around the globe who log into our system. The system is Terminal Server and it is set up to their local language. If the client resides in England and only speaks English a project manager from France or Spain should still be able to send our client an invoice with English dates. It would not be resonable to ask a project manager to change his/her system language each time they were to send something to a client in a different country.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33458471
Then this will not easily be possible.
Again, you can't really "Switch" the country setting for MS Access.
Changing the country will change every country setting for every program and Utility on your system.

You may be able to do this with the Language Packs I mentioned above though...

JeffCoachman
0
 
LVL 1

Author Comment

by:Shawn
ID: 33458850
all language packs are already installed.

thanks anyway Jeff. I'm going to leave this question open for a while in case anyone has a possible solution/workaround.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33460632
OK
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33580278
<no solution found>

The solution was that this cannot be easily be done.

<Then this will not easily be possible.>
(post:33458471)
0
 
LVL 1

Author Comment

by:Shawn
ID: 33580393
I really don't want to argue but I hardly think a comment "The solution was that this cannot be easily be done." merits points.

I know this and this is why I am looking for expert help. I will leave the question open a little longer and request attention for help....to find a working solution.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33580738
Actually the language: "Object" is a little strong.

It sounds like I am arguing..., but I'm not.
;-)

I would rather it say: "Clarify"
;-)

You can even request that a notification be sent out to the experts again, to re-ignites some interest.


;-)

Jeff
0
 
LVL 1

Author Comment

by:Shawn
ID: 33582239
hi Jeff. no feelings hurt. I was a little worried my comment was too strong. glad all is good :-)

I mada a request so let's see if anyone is interested.
thanks, Shawn
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33582760
MO,

Yeah...

"Objection Posted" sounds a little strong.
Perhaps something like: "Review" would be a better sounding option?
It could still stop the Delete request, but in a nicer way...

;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33582768
In any event, I will have no issues with the manner in which the asker chooses to close this question.

;-)

Jeff
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 33608258
Hi Shawnaraxi
Yes, you CAN do it!  :-)
The attached code from Michael Kaplan shows how to use the locale APIs to format dates, times and currencies according to the the Windows locale code (LCID).
For example,
FormatDateTimeIntl( Date, vbLongDate, 1035 ) gives "06. syyskuuta 2010"  (Finnish)
FormatDateTimeIntl( Date, vbLongDate, 1036 ) gives "6 septembre 2010"  (French)
FormatDateTimeIntl( Date, vbLongDate, 1038 ) gives "2010. szeptember 6."  (Hungarian)
If you just want the month, use the GetLocaleInfo API - for example:
StGetLocaleInfo( 1045, LOCALE_SMONTHNAME3 ) gives "marzec"  (Polish for "March")
--
Graham

'------------------------------------------
'   basIntlFormats
'
'   Some helper functions for handling date/time/currency
'   formats for locales other than the current regional
'   settings will handle. Since VB relies on the control
'   panel, this is the only way to accomplish this.
'
'   You may use this code in your projects, but a note on who
'   you got it from would be appreciated. :-)
'
'   Version of this module is 2.1.
'
'   HISTORY:
'   1.0     5/5/98      Added date/time enum info
'   1.1     12/1/98     Used Get[Time|Date]Format instead of VB's Format function
'   1.2     5/5/99      Added currency formatting support
'   1.3     10/1/99     Added locale validation code
'   2.0     10/1/99     Added NT5 constants
'   2.1     10/1/99     Added comments on the validation code
'
'   (c) 1998-99 Trigeminal Software, Inc.  All Rights Reserved
'------------------------------------------
Option Explicit
Option Compare Text

'------------------------------------------------------------
' LOCALE specifiers -- from OLENLS.H
'------------------------------------------------------------
Public Enum LCTypeEnum
    LOCALE_NOUSEROVERRIDE = &H80000000             ' OR in to avoid user override
    LOCALE_ILANGUAGE = &H1                         ' language id
    LOCALE_SLANGUAGE = &H2                         ' localized name of language
    LOCALE_SENGLANGUAGE = &H1001                   ' English name of language
    LOCALE_SABBREVLANGNAME = &H3                   ' abbreviated language name
    LOCALE_SNATIVELANGNAME = &H4                   ' native name of language
    LOCALE_ICOUNTRY = &H5                          ' country code
    LOCALE_SCOUNTRY = &H6                          ' localized name of country
    LOCALE_SENGCOUNTRY = &H1002                    ' English name of country
    LOCALE_SABBREVCTRYNAME = &H7                   ' abbreviated country name
    LOCALE_SNATIVECTRYNAME = &H8                   ' native name of country
    LOCALE_IDEFAULTLANGUAGE = &H9                  ' default language id
    LOCALE_IDEFAULTCOUNTRY = &HA                   ' default country code
    LOCALE_IDEFAULTCODEPAGE = &HB                  ' default oem code page
    LOCALE_IDEFAULTANSICODEPAGE = &H1004           ' default ansi code page
    LOCALE_SLIST = &HC                             ' list item separator
    LOCALE_IMEASURE = &HD                          ' 0 = metric, 1 = US
    LOCALE_SDECIMAL = &HE                          ' decimal separator
    LOCALE_STHOUSAND = &HF                         ' thousand separator
    LOCALE_SGROUPING = &H10                        ' digit grouping
    LOCALE_IDIGITS = &H11                          ' number of fractional digits
    LOCALE_ILZERO = &H12                           ' leading zeros for decimal
    LOCALE_INEGNUMBER = &H1010                     ' negative number mode
    LOCALE_SNATIVEDIGITS = &H13                    ' native ascii 0-9
    LOCALE_SCURRENCY = &H14                        ' local monetary symbol
    LOCALE_SINTLSYMBOL = &H15                      ' intl monetary symbol
    LOCALE_SMONDECIMALSEP = &H16                   ' monetary decimal separator
    LOCALE_SMONTHOUSANDSEP = &H17                  ' monetary thousand separator
    LOCALE_SMONGROUPING = &H18                     ' monetary grouping
    LOCALE_ICURRDIGITS = &H19                      ' # local monetary digits
    LOCALE_IINTLCURRDIGITS = &H1A                  ' # intl monetary digits
    LOCALE_ICURRENCY = &H1B                        ' positive currency mode
    LOCALE_INEGCURR = &H1C                         ' negative currency mode
    LOCALE_SDATE = &H1D                            ' date separator
    LOCALE_STIME = &H1E                            ' time separator
    LOCALE_SSHORTDATE = &H1F                       ' short date-time separator
    LOCALE_SLONGDATE = &H20                        ' long date-time separator
    LOCALE_STIMEFORMAT = &H1003                    ' time format string
    LOCALE_IDATE = &H21                            ' short date format ordering
    LOCALE_ILDATE = &H22                           ' long date format ordering
    LOCALE_ITIME = &H23                            ' time format specifier
    LOCALE_ITIMEMARKPOSN = &H1005                  ' time marker position
    LOCALE_ICENTURY = &H24                         ' century format specifier
    LOCALE_ITLZERO = &H25                          ' leading zeros in time field
    LOCALE_IDAYLZERO = &H26                        ' leading zeros in day field
    LOCALE_IMONLZERO = &H27                        ' leading zeros in month field
    LOCALE_S1159 = &H28                            ' AM designator
    LOCALE_S2359 = &H29                            ' PM designator
    LOCALE_ICALENDARTYPE = &H1009                  ' type of calendar specifier
    LOCALE_IOPTIONALCALENDAR = &H100B              ' additional calendar types specifier
    LOCALE_IFIRSTDAYOFWEEK = &H100C                ' first day of week specifier
    LOCALE_IFIRSTWEEKOFYEAR = &H100D               ' first week of year specifier
    LOCALE_SDAYNAME1 = &H2A                        ' long name for Monday
    LOCALE_SDAYNAME2 = &H2B                        ' long name for Tuesday
    LOCALE_SDAYNAME3 = &H2C                        ' long name for Wednesday
    LOCALE_SDAYNAME4 = &H2D                        ' long name for Thursday
    LOCALE_SDAYNAME5 = &H2E                        ' long name for Friday
    LOCALE_SDAYNAME6 = &H2F                        ' long name for Saturday
    LOCALE_SDAYNAME7 = &H30                        ' long name for Sunday
    LOCALE_SABBREVDAYNAME1 = &H31                  ' abbreviated name for Monday
    LOCALE_SABBREVDAYNAME2 = &H32                  ' abbreviated name for Tuesday
    LOCALE_SABBREVDAYNAME3 = &H33                  ' abbreviated name for Wednesday
    LOCALE_SABBREVDAYNAME4 = &H34                  ' abbreviated name for Thursday
    LOCALE_SABBREVDAYNAME5 = &H35                  ' abbreviated name for Friday
    LOCALE_SABBREVDAYNAME6 = &H36                  ' abbreviated name for Saturday
    LOCALE_SABBREVDAYNAME7 = &H37                  ' abbreviated name for Sunday
    LOCALE_SMONTHNAME1 = &H38                      ' long name for January
    LOCALE_SMONTHNAME2 = &H39                      ' long name for February
    LOCALE_SMONTHNAME3 = &H3A                      ' long name for March
    LOCALE_SMONTHNAME4 = &H3B                      ' long name for April
    LOCALE_SMONTHNAME5 = &H3C                      ' long name for May
    LOCALE_SMONTHNAME6 = &H3D                      ' long name for June
    LOCALE_SMONTHNAME7 = &H3E                      ' long name for July
    LOCALE_SMONTHNAME8 = &H3F                      ' long name for August
    LOCALE_SMONTHNAME9 = &H40                      ' long name for September
    LOCALE_SMONTHNAME10 = &H41                     ' long name for October
    LOCALE_SMONTHNAME11 = &H42                     ' long name for November
    LOCALE_SMONTHNAME12 = &H43                     ' long name for December
    LOCALE_SMONTHNAME13 = &H100E                   ' long name for 13th month (if exists)
    LOCALE_SABBREVMONTHNAME1 = &H44                ' abbreviated name for January
    LOCALE_SABBREVMONTHNAME2 = &H45                ' abbreviated name for February
    LOCALE_SABBREVMONTHNAME3 = &H46                ' abbreviated name for March
    LOCALE_SABBREVMONTHNAME4 = &H47                ' abbreviated name for April
    LOCALE_SABBREVMONTHNAME5 = &H48                ' abbreviated name for May
    LOCALE_SABBREVMONTHNAME6 = &H49                ' abbreviated name for June
    LOCALE_SABBREVMONTHNAME7 = &H4A                ' abbreviated name for July
    LOCALE_SABBREVMONTHNAME8 = &H4B                ' abbreviated name for August
    LOCALE_SABBREVMONTHNAME9 = &H4C                ' abbreviated name for September
    LOCALE_SABBREVMONTHNAME10 = &H4D               ' abbreviated name for October
    LOCALE_SABBREVMONTHNAME11 = &H4E               ' abbreviated name for November
    LOCALE_SABBREVMONTHNAME12 = &H4F               ' abbreviated name for December
    LOCALE_SABBREVMONTHNAME13 = &H100F             ' abbreviated name for 13th month (if exists)
    LOCALE_SPOSITIVESIGN = &H50                    ' positive sign
    LOCALE_SNEGATIVESIGN = &H51                    ' negative sign
    LOCALE_IPOSSIGNPOSN = &H52                     ' positive sign position
    LOCALE_INEGSIGNPOSN = &H53                     ' negative sign position
    LOCALE_IPOSSYMPRECEDES = &H54                  ' mon sym precedes pos amt
    LOCALE_IPOSSEPBYSPACE = &H55                   ' mon sym sep by space from pos
    LOCALE_INEGSYMPRECEDES = &H56                  ' mon sym precedes neg amt
    LOCALE_INEGSEPBYSPACE = &H57                   ' mon sym sep by space from neg */
End Enum

' Locale enumeration flags from winnls.h
Private Const LCID_INSTALLED = &H1                    '/* installed locale ids */
Private Const LCID_SUPPORTED = &H2                    '/* supported locale ids */

' dwFlags values for EnumDateFormats
Private Const DATE_SHORTDATE = &H1                     ' use short date picture
Private Const DATE_LONGDATE = &H2                      ' use long date picture
Private Const DATE_USE_ALT_CALENDAR = &H4              ' use alternate calendar (if any)
#If (WINVER >= &H500) Then
Private Const DATE_YEARMONTH = &H8                    ' use year month picture
Private Const DATE_LTRREADING = &H10                  ' add marks for left to right reading order layout
Private Const DATE_RTLREADING = &H20                  ' add marks for right to left reading order layout
#End If ' WINVER >= &h0500

Private Type CURRENCYFMT
        NumDigits As Long '  number of decimal digits
        LeadingZero As Long '  if leading zero in decimal fields
        Grouping As Long '  group size left of decimal
        lpDecimalSep As String              '  ptr to decimal separator string
        lpThousandSep As String             '  ptr to thousand separator string
        NegativeOrder As Long '  negative currency ordering
        PositiveOrder As Long '  positive currency ordering
        lpCurrencySymbol As String          '  ptr to currency symbol string
End Type

Private Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
End Type

Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString As Long) As Long
Private Declare Sub RtlMoveMemory Lib "kernel32" (pDest As Any, pSrc As Any, ByVal ByteLen As Long)

' Supported by NT4, Windows 95, Windows 98
Private Declare Function ConvertDefaultLocale Lib "kernel32" (ByVal LCID As Long) As Long
Private Declare Function EnumDateFormats Lib "kernel32" Alias "EnumDateFormatsA" (ByVal lpDateFmtEnumProc As Long, ByVal locale As Long, ByVal dwFlags As Long) As Boolean
Private Declare Function EnumTimeFormats Lib "kernel32" Alias "EnumTimeFormatsA" (ByVal lpTimeFmtEnumProc As Long, ByVal locale As Long, ByVal dwFlags As Long) As Boolean
Private Declare Function GetCurrencyFormat Lib "kernel32" Alias "GetCurrencyFormatA" (ByVal locale As Long, ByVal dwFlags As Long, ByVal lpValue As String, lpFormat As CURRENCYFMT, ByVal lpCurrencyStr As String, ByVal cchCurrency As Long) As Long
Private Declare Function GetDateFormat Lib "kernel32" Alias "GetDateFormatA" (ByVal locale As Long, ByVal dwFlags As Long, lpDate As SYSTEMTIME, ByVal lpFormat As String, ByVal lpDateStr As String, ByVal cchDate As Long) As Long
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal LCID As Long, ByVal LCTYPE As Long, lpData As Any, ByVal cchData As Integer) As Integer
Private Declare Function GetUserDefaultLangID Lib "kernel32" () As Integer
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
Private Declare Function GetSystemDefaultLangID Lib "kernel32" () As Integer
Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long
Private Declare Function GetTimeFormat Lib "kernel32" Alias "GetTimeFormatA" (ByVal locale As Long, ByVal dwFlags As Long, lpTime As SYSTEMTIME, ByVal lpFormat As String, ByVal lpTimeStr As String, ByVal cchTime As Long) As Long
Private Declare Function IsValidLocale Lib "kernel32" (ByVal locale As Long, ByVal dwFlags As Long) As Boolean
Private Declare Function VariantTimeToSystemTime Lib "oleaut32.dll" (ByVal vtime As Double, lpSystemTime As SYSTEMTIME) As Boolean
 
' Not used at present -- Windows 2000-specific functions relating to the UI language.
' CONSIDER: Use them for enhanced NT5 support?
Private Declare Function GetUserDefaultUILanguage Lib "kernel32" () As Long
Private Declare Function GetSystemDefaultUILanguage Lib "kernel32" () As Long

Private m_stFormatEnum As String

'----------------------------------------------------------------------
' StGetLocaleInfo
'
'   Gets Locale (international) info about current config
'   See LOCALE constants at top of module for LCTYPE values
'----------------------------------------------------------------------
Public Function StGetLocaleInfo(locale As Long, LCTYPE As LCTypeEnum) As String
    Dim LCID As Long
    Dim stBuff As String * 255
    
    'ask for the locale info
    If (GetLocaleInfo(locale, LCTYPE, ByVal stBuff, Len(stBuff)) > 0) Then
        StGetLocaleInfo = StFromSz(stBuff)
    End If

End Function

'------------------------------------------------------------
' StFromSz
'
'   Returns a truncated string given a null terminated string
'------------------------------------------------------------g
Public Function StFromSz(szTmp As String) As String
    Dim ich As Integer
    ich = InStr(1, szTmp, vbNullChar, vbBinaryCompare)
    If ich Then
        StFromSz = Left$(szTmp, ich - 1)
    Else
        StFromSz = szTmp
    End If
End Function

Public Function StDateEnum(ByVal locale As Long, Optional ByVal fShortDate As Boolean = True) As String
    m_stFormatEnum = vbNullString
    Call EnumDateFormats(AddressOf EnumFormatsProc, locale, IIf(fShortDate, DATE_SHORTDATE, DATE_LONGDATE))
    StDateEnum = m_stFormatEnum
End Function

Public Function StTimeEnum(ByVal locale As Long) As String
    m_stFormatEnum = vbNullString
    Call EnumTimeFormats(AddressOf EnumFormatsProc, locale, 0&)
    StTimeEnum = m_stFormatEnum
End Function

Public Function EnumFormatsProc(ByVal lpFormatString As Long) As Long
    Dim st As String
    
    st = String$(lstrlen(lpFormatString), vbNullChar)
    RtlMoveMemory ByVal StrPtr(st), ByVal lpFormatString, lstrlen(lpFormatString)
    st = StFromSz(StrConv(st, vbUnicode))
    
    ' Grab the first format
    m_stFormatEnum = st
    
    ' Stop enumerating after the first one (there is usually only one anyway)
    EnumFormatsProc = 1&
End Function

'------------------------------
'   FormatDateTimeIntl
'
'   Provides a slightly better version of FormatDateTime which will allow
'   you to use format strings other than the ones for the current regional
'   settings
'------------------------------
Public Function FormatDateTimeIntl(Expression As Variant, _
 Optional NamedFormat As VbDateTimeFormat = vbGeneralDate, _
 Optional locale As Long = -1)
    Dim stDateFormat As String
    Dim stTimeFormat As String
    Dim stDateBuffer As String
    Dim stTimeBuffer As String
    Dim st As SYSTEMTIME
    Dim cch As Long
    
    If (IsValidLocale(locale, LCID_SUPPORTED) = 0) Then
        ' Either they are wanting the default locale, or the locale they specified
        ' is invalid, so use the normal FormatDateTime function. This works since
        ' the default value (-1) is not a supported locale
        ' CONSIDER: NOT a perfect solution, perhaps an error should be raised
        ' when an invalid LCID is passed?
        FormatDateTimeIntl = FormatDateTime(Expression, NamedFormat)
        Exit Function
    End If
    
    Select Case NamedFormat
        Case vbGeneralDate
            stDateFormat = StDateEnum(locale, False)
            stTimeFormat = StTimeEnum(locale)
        Case vbLongDate
            stDateFormat = StDateEnum(locale, False)
        Case vbShortDate
            stDateFormat = StDateEnum(locale, True)
            
        Case vbLongTime
            stTimeFormat = StTimeEnum(locale)
        Case vbShortTime
            ' Since VB does not use regional settings for this format, neither
            ' do we. Display a time using the 24-hour format (hh:mm)
            stTimeFormat = "hh" & StGetLocaleInfo(locale, LOCALE_STIME) & "mm"
    End Select
    
    If (NamedFormat = vbGeneralDate) Or (NamedFormat = vbLongDate) Or (NamedFormat = vbShortDate) Then
        If VariantTimeToSystemTime(Expression, st) Then
            cch = GetDateFormat(locale, 0&, st, stDateFormat, vbNullString, 0&)
            If cch > 0 Then
                stDateBuffer = String$(cch, vbNullChar)
                If GetDateFormat(locale, 0&, st, stDateFormat, stDateBuffer, Len(stDateBuffer)) > 0 Then
                    stDateBuffer = StFromSz(stDateBuffer)
                Else
                    Err.Raise vbObjectError + 3000, "basIntlformats.FormatDateTimeIntl", "Failed GetDateFormat call, GetLastError returns: " & Err.LastDllError
                End If
            End If
        Else
            Err.Raise vbObjectError + 3000, "basIntlformats.FormatDateTimeIntl", "Failed VariantTimeToSystemTime call, GetLastError returns: " & Err.LastDllError
        End If
    End If
    
    If (NamedFormat = vbGeneralDate) Or (NamedFormat = vbLongTime) Or (NamedFormat = vbShortTime) Then
        If VariantTimeToSystemTime(Expression, st) Then
            cch = GetTimeFormat(locale, 0&, st, stTimeFormat, vbNullString, 0&)
            If cch > 0 Then
                stTimeBuffer = String$(cch, vbNullChar)
                If GetTimeFormat(locale, 0&, st, stTimeFormat, stTimeBuffer, Len(stTimeBuffer)) > 0 Then
                    stTimeBuffer = StFromSz(stTimeBuffer)
                Else
                    Err.Raise vbObjectError + 3000, "basIntlformats.FormatDateTimeIntl", "Failed GetDateFormat call, GetLastError returns: " & Err.LastDllError
                End If
            End If
        Else
            Err.Raise vbObjectError + 3000, "basIntlformats.FormatDateTimeIntl", "Failed VariantTimeToSystemTime call, GetLastError returns: " & Err.LastDllError
        End If
    End If
    
    If NamedFormat = vbGeneralDate Then
        FormatDateTimeIntl = stDateBuffer & " " & stTimeBuffer
    Else
        FormatDateTimeIntl = stDateBuffer & stTimeBuffer
    End If
End Function


'------------------------------
'   FormatCurrencyIntl
'
'   Provides a slightly better version of FormatCurrency which will allow
'   you to use format strings other than the ones for the current regional
'   settings...
'------------------------------
Function FormatCurrencyIntl(Expression As Variant, _
 Optional NumDigitsAfterDecimal As Long = -1, _
 Optional IncludeLeadingDigit As VbTriState = vbUseDefault, _
 Optional UseParensForNegativeNumbers As VbTriState = vbUseDefault, _
 Optional GroupDigits As VbTriState = vbUseDefault, _
 Optional locale As Long = -1) As String

    Dim cf As CURRENCYFMT
    Dim nc As Integer
    Dim stGrouping As String
    Dim stBuffer As String
    Dim ich As Long
    Dim cch As Long
    
    If (IsValidLocale(locale, LCID_SUPPORTED) = 0) Then
        ' Either they are wanting the default locale, or the locale they specified
        ' is invalid, so use the normal FormatCurrency function. This works since
        ' the default value (-1) is not a supported locale.
        ' CONSIDER: NOT a perfect solution, perhaps an error should be raised
        ' when an invalid LCID is passed?
        FormatCurrencyIntl = FormatCurrency(Expression, NumDigitsAfterDecimal, IncludeLeadingDigit, UseParensForNegativeNumbers, GroupDigits)
        Exit Function
    End If
    
    If NumDigitsAfterDecimal = -1 Then
        cf.NumDigits = StGetLocaleInfo(locale, LOCALE_IDIGITS)
    Else
        cf.NumDigits = NumDigitsAfterDecimal
    End If
    
    If IncludeLeadingDigit = vbUseDefault Then
        cf.LeadingZero = StGetLocaleInfo(locale, LOCALE_ILZERO)
    Else
        cf.LeadingZero = Abs(IncludeLeadingDigit)
    End If
    
    Select Case UseParensForNegativeNumbers
        Case vbUseDefault
            cf.NegativeOrder = StGetLocaleInfo(locale, LOCALE_INEGCURR)
        Case vbTrue
            If StGetLocaleInfo(locale, LOCALE_INEGSYMPRECEDES) = 1 Then
                cf.NegativeOrder = 0    ' Left parenthesis,monetary symbol,number,right parenthesis. Example: ($1.1)
            Else
                cf.NegativeOrder = 4    ' Left parenthesis, number, monetary symbol, right parenthesis. Example: (1.1$)
            End If
        Case vbFalse
            Select Case StGetLocaleInfo(locale, LOCALE_INEGSIGNPOSN)
                Case 0  ' Parentheses surround the amount and the monetary symbol.
                    ' Moral dilemma... the user has said to NOT use parens for
                    ' negative currency, but the control panel says we should
                    ' and does not give us the order. Try to derive it
                     nc = StGetLocaleInfo(locale, LOCALE_INEGCURR)
                    Select Case nc
                        Case 0, 14  ' Left parenthesis,monetary symbol,number,right parenthesis. Example: ($1.1) -- 14 has extra space
                            cf.NegativeOrder = 1    ' Negative sign, monetary symbol, number. Example: -$1.1
                        Case 4, 15 ' Left parenthesis, number, monetary symbol, right parenthesis. Example: (1.1$) -- 15 has extra space
                            cf.NegativeOrder = 7    '  Number, monetary symbol, negative sign. Example: 1.1$-
                        Case Else
                         cf.NegativeOrder = nc
                    End Select
                    
                Case 1  ' The sign precedes the number.
                    cf.NegativeOrder = 2    ' Monetary symbol, negative sign, number. Example: $-1.1
                Case 2  ' The sign follows the number.
                    cf.NegativeOrder = 3    ' Monetary symbol, number, negative sign. Example: $1.1-
                Case 3  ' The sign precedes the monetary symbol.
                    cf.NegativeOrder = 1    ' Negative sign, monetary symbol, number. Example: -$1.1
                Case 4  ' The sign follows the monetary symbol.
                    cf.NegativeOrder = 7    ' Number, monetary symbol, negative sign. Example: 1.1$-
            End Select
    End Select
    
    Select Case GroupDigits
        Case vbUseDefault, vbTrue
            stGrouping = StGetLocaleInfo(locale, LOCALE_SMONGROUPING)
            ich = InStr(1, stGrouping, ";", vbBinaryCompare)
            If ich > 0 Then stGrouping = Left$(stGrouping, ich - 1)
            If IsNumeric(stGrouping) Then
                cf.Grouping = Val(stGrouping)
            Else
                cf.Grouping = 3
            End If
        Case vbFalse
            cf.Grouping = 0
    End Select
    
    
    ' Get the values that the function does not have params for from the info
    ' for the given locale
    cf.lpCurrencySymbol = StGetLocaleInfo(locale, LOCALE_SCURRENCY)
    cf.lpDecimalSep = StGetLocaleInfo(locale, LOCALE_SMONDECIMALSEP)
    cf.lpThousandSep = StGetLocaleInfo(locale, LOCALE_STHOUSAND)
    cf.PositiveOrder = StGetLocaleInfo(locale, LOCALE_ICURRENCY)
    
    cch = GetCurrencyFormat(locale, 0&, CStr(Expression), cf, vbNullString, 0&)
    If cch > 0 Then
        stBuffer = String$(cch, vbNullChar)
        If GetCurrencyFormat(locale, 0&, CStr(Expression), cf, stBuffer, Len(stBuffer)) > 0 Then
            FormatCurrencyIntl = StFromSz(stBuffer)
        Else
            Err.Raise vbObjectError + 3000, "basIntlformats.FormatCurrencyIntl", "Failed GetCurrencyFormat call, GetLastError returns: " & Err.LastDllError
        End If
    End If
    
End Function

Open in new window

0
 

Expert Comment

by:jonsgould
ID: 33608264
if u r only concerned with two languages, show them both. Virtually every traffic sign, train instructions and almost everything else the public see these days are presented in more than one language. It is the norm rather than the exception.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 33608269
PS:  You can find a list of LCIDs here...
http://msdn.microsoft.com/en-us/goglobal/bb964664.aspx 
0
 
LVL 1

Author Comment

by:Shawn
ID: 33608788
sweet Graham. I'll give it a try tomorrow. Looks good though.
cheers
Shawn
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33615845
Graham,

looks interesting.
;-)

How would it be set up to perform as the asker requested?:

<If English is selected in the database the month should be displayed in English regardless of the the system language.>

JeffCoachman
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 33616116
Hi Jeff
The LCID for English (UK) is 2057 (or 1033 if you prefer US English ;-)
So if you use one of those LCIDs in the call to FormatDateTimeIntl, then the display would be in that language, no matter what the system language was set to.
Presumably Shawn has a database where different records are assigned different languages - for example, multi-lingual clients.  All he needs is a table mapping the language name (or whatever he has in his Clients table) to the appropriate LCID.
--
Graham
0
 
LVL 1

Author Comment

by:Shawn
ID: 33617675
Graham,

seems I'm close but the text box is still showing "error".

I've pasted the code in a module and saved it as "basIntlFormats".

Then in my textbox I tried =FormatDateTimeIntl([DateDue],"mmm dd yy",1036) <---1036 is French

been a while since I've played with modules. What am I missing here?
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 2000 total points
ID: 33622155
Hi Shawn
The second argument is declared as:
Optional NamedFormat As VbDateTimeFormat = vbGeneralDate
Note then that the only valid values are numeric values corresponding to predeclared vbDateTimeFormat values.  These are:
0 = vbGeneralDate
1 = vbLongDate
2 = vbShortDate
3 = vbLongTime
4 = vbShortTime
You can only use the named constants in VBA, so in SQL or a ContolSource expression you must use the numeric values.  So for a short date format in French you could use:
=FormatDateTimeIntl( [DateDue], 2, 1036 )
This is probably better than an explicit "mmm dd yy" format, as different languages are used to seeing the day, month and year in different orders.
If you want to run the "mmm dd yy" format, then you can still do it with your own function using StGetLocaleInfo to find the abbreviated month in the given language:
Function MyFormatDate(Expression As Variant, Optional Locale As Long) As Variant
' Format date as "<month abbrev> dd yy" in given locale
  If Not IsDate(Expression) Then
    MyFormatDate = Expression
  Else
    If (IsValidLocale(Locale, LCID_SUPPORTED) = 0) Then
      ' LCID not supplied or not supported - use system settings
      MyFormatDate = Format(Expression, "mmm dd yy")
    Else
      ' get abbreviated month name and append " dd yy"
      ' we can do this because all the LOCALE_SABBREVMONTHNAMExx values are consecutive
      MyFormatDate = StGetLocaleInfo(Locale, Month(Expression) + LOCALE_SABBREVMONTHNAME1 - 1 )  _
          & Format(Expression, " dd yy")
    End If
  End If
End Function
Then use:
=MyFormatDate( [DueDate], 1036 )
Note that the abbreviated month will not always be three characters!
--
Graham
0
 
LVL 1

Author Comment

by:Shawn
ID: 33623110
wow, better than I expected. Tired both methods and they work flawlessly.

thank you again Graham!


Shawn
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 33623150
That's great!
I'm happy we were able to perform the impossible ;-)
Good luck!
--
Graham
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question