Solved

date language in report

Posted on 2010-08-16
30
1,408 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 500 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now