Splitting date apart

[RexesOperator]

I promised I wouldn't ask a question until after deployment - well I got official approval for the db this morning - depending on one change.

I need to be able to convert the date from long form Canadian English to long form French so the month names would appear in French on the French language letters and document. I have the regional settings set to English(Canada)

My most recent attempt is (this looked the most likely of the others I tried):

=IIf(Month([DATESENT])=1,[DATESENT]=" Janvier" & " " & Day([DATESENT]) & ", " & Year([DATESENT])," ")

In this case it correctly displays a blank if the month is not January. If it Januaray I get "December 30, 1899" (which I assume is the date used to begin Access's date calculations.

 

[pbaldy]

There's got to be a better way, but I think it would be:

=IIf(Month([DATESENT])=1,"Janvier " & Day([DATESENT]) & ", " & Year([DATESENT])," ")

I wonder if you could have a table with the month number, English and French names, and just join to it?

 

[datAdrenaline]

In this case I would suggest the use of the Choose() function in conjuction with the Format() function ...

Choose(Month([DATESENT]),"Janvier","Février","Mars","..etc..") & " " & Format([DATESENT], "d, yyyy")

 

[RexesOperator]

There's got to be a better way, but I think it would be:

=IIf(Month([DATESENT])=1,"Janvier " & Day([DATESENT]) & ", " & Year([DATESENT])," ")

I wonder if you could have a table with the month number, English and French names, and just join to it?

Thank you kind Sir!

You have just made my manager a happy man (and me a happy lady!)

I am sure you are right about the table. Another option I looked at was a case statement, but I wasn't sure where to put it on the report. I tried OnLoad and OnActivate, but both produced a #Error. Likely my fault in the coding (it usually is)This works like a charm and found my error.

 

[datAdrenaline]

Any reason the Choose() wouldn't work? ... No need for VBA or a Table .. ??? ... just curious as I am thrilled a solution posted does the trick for you! ... but just wondering the advantage of the IIf() over the Choose() ...

 

[RexesOperator]

I didn't actually try it because Paul found my error. You're right - this is much simpler and cleaner than a 12 step iif statement. Thanks for your insight!

 

[pbaldy]

I agree with Brent about Choose; I posted what I did so you'd know what you did wrong. That said, I'd still be curious whether a table wouldn't be the best solution. I've never had to do a multilingual db, so not really sure what the best way would be.

 

[RexesOperator]

This isn't truly a bilingual db - only the form letters that go out to clients with their results are in both French and English. So I only need to format four textboxes (the date on four reports).

You are right about the table concept. I used that concept very early on in the development of this monster with code I found here:

http://www.fontstuff.com/access/acctut13a.htm (actually there is a nice tutorial on changing the appearance of tabs for A2K & A97 as well)

Then I discovered Stephen Lebans' site.

The nice thing about the Choose function is I just dropped it into the text box as needed.

Anyway - you're both right and I'm happy. What a way to start off the New Year!