Would like a module, if possible (1 Viewer)

[Valery]

Hi all!

I have create a series of text boxes through much hardship (newbie!) to change a given date, in a given report, into French. I have save it in a report so I can easily retrieve the text boxes and copy and edit them for other reports. However, this is really cumbersome and may be even more for the users.

I would like to have a module - if possible - perform these expressions.

Here are the text boxes by name, control source and format (when useful to you). Please don't laugh too hard - it's all I knew to do

txtCurDate
=Now()
dd mmm yyyy

txtMonthNum
=DatePart("m",[txtCurDate])

txtDayNum
=IIf([txtDayNum]="1","1er",[txtDayNum])

txtMonthFre

=IIf([txtMonthNum]="1","janvier",IIf([txtMonthNum]="2","février",IIf([txtMonthNum]="3","mars",IIf([txtMonthNum]="4","avril",IIf([txtMonthNum]="5","mai",IIf([txtMonthNum]="6","juin",IIf([txtMonthNum]="7","juillet",IIf([txtMonthNum]="8","août",IIf([txtMonthNum]="9","septembre",IIf([txtMonthNum]="10","octobre",IIf([txtMonthNum]="11","novembre",IIf([txtMonthNum]="12","décembre"))))))))))))

txtDateFre

="le" & " " & [txtDayFre] & " " & [txtMonthFre] & " " & DatePart("yyyy",[txtCurDate])

Therefore a date like 29-Feb-2006 become – Le 29 février 2006. Note: In French the first day of any month is written as: 1st which is - 1er - in French. Which is why I have the text box – txtDayNum.

For now, I copy all the codes and replace the field names with the date field name that needs to be in French!

Thank you!

 

[gemma-the-husky]

isn't there a setting for a locale of "France"?

If so, it may well automatically render any date into the appropriate French equivalent without you needing to do anything.

obviously if you are in the UK doing this, then isn't so simple.

 

[Valery]

No - the settings must remain English-Canada. I only need the date for the many French Reports. The entire database (and the PC) is English based.

 

[gemma-the-husky]

hmm.

Surely some Canadian users have already solved this issue. It must be common enough in Canada to need multi-language output.

I am going out, but if you don't get a fix, I will knock up a simple module for you tomorrow.

 

[Valery]

THANK YOU - I know it will help many of us!

 

[arnelgp]

paste this in a module an try on your report:

Public Function fnENdate2FR(ByVal vParam As Variant) As String

    Dim arrFrenchMonths As Variant
    
    arrFrenchMonths = Array("janvier", "février", "mars", _
                            "avril", "mai", "juin", _
                            "juillet", "août", "septembre", _
                            "octobre", "novembre", "décembre")
    vParam = "" & vParam
    If Not IsDate(vParam) Then
        fnENdate2FR = vParam
    
    Else
        vParam = CDate(vParam)
        fnENdate2FR = IIf(Day(vParam) = 1, Day(vParam) & "er", "le " & Day(vParam)) & " " & _
                            arrFrenchMonths(Month(vParam) - 1) & " " & Year(vParam)
    End If
    
End Function

set the Control source of your textbox in report to:

=fnENDate2FR(Date)

if it is a Label control, use the Forms Load event to set the caption of this label:

Private Sub Report_Load()
Me.label.Caption = fnENdate2FR(Date)
End Sub

 

[Valery]

Totally Incredible! Works perfectly! THANK YOU!!!!!!!

Just curious - how in the codes does it say that January = janvier? What kind of course could I take to learn how to write these wonderful functions? I am totally amazed by the difficulty and the simplicity!

 

[arnelgp]

remember we declare an array variables for the french months (arrFrenchMonths).

then we take the Month number of the date you passed by using Month(vParam).
this will return 3 for march, and since arrays are zero based (we did not explicit instruct the module to start at 1 in module declaration. to explicitly start our array at 1 we declare in a module Option Base 1), we subtract 1 from Month(vParam) and get that value from our array: arrFrenchMonths( Month(vParam) - 1 ).

 

[Valery]

WOW! TY Any chance you could lend a hand on the post (thread?): Re: Doable??? Label Report with a grouping.

Would be great to have your assistance.