date format

[Jaymac]

Hi folks

I have a large database which has various fields with dates held in
numerical format. I would like days to be shown in mail merge documents as,
eg 1st 22nd 3rd, etc. and have tried using the pattern "Dte"\@ "dddd, d MMMM,
yyyy" in the mergefield, but this only gives me the US format of the date-day
as a number.

Does anyone have any bright ideas?

Many thanks

Jack

PS I have also posted this question in office general

 

[Douglas J. Steele]

Try using the Format function (not the Format property) in the query you're
exporting.

In other words, rather than

SELECT Field1, Field2, DateField FROM Table

use

SELECT Field1, Field2, Format(DateField, "dddd, d mmmm, yyyy") AS
FormattedDateField FROM Table

 

[Jaymac]

Thanks for responding Douglas

However this gives the same results as I was getting e.g. 1 January when I
want 1st January, 3 January for 3rd January etc.

Do you have any other suggestions?

Jack

 

[John Vinson]

Hi folks

I have a large database which has various fields with dates held in
numerical format. I would like days to be shown in mail merge documents as,
eg 1st 22nd 3rd, etc. and have tried using the pattern "Dte"\@ "dddd, d MMMM,
yyyy" in the mergefield, but this only gives me the US format of the date-day
as a number.

You'll probably need to go beyond the Format function to get this. Try
this (partially tested air code):

Format([datefield], "dddd, d") &
Switch(Day([datefield]) = 1, "st",
Day([datefield]) = 21, "st",
Day([datefield]) = 31, "st",
Day([datefield]) = 2, "nd",
Day([datefield]) = 22, "nd",
Day([datefield]) = 3, "rd",
Day([datefield]) = 23, "rd",
True, "th") &
Format([datefield], " mmmm, yyyy")

You could also implement the same logic in a little VBA function... oh
why not...

Public Function OrdinalDate(dtIn As Date) As String
OrdinalDate = Format(dtIn, "dddd, d") ' e.g. Thursday, 11
Select Case Day(dtIn)
Case 1, 21, 31
OrdinalDate = OrdinalDate & "st "
Case 2, 22
OrdinalDate = OrdinalDate & "nd "
Case 3, 23
OrdinalDate = OrdinalDate & "rd "
Case Else
OrdinalDate = OrdinalDate & "th "
End Select
OrdinalDate = OrdinalDate & Format(dtIn, "mmmm, yyyy")
End Function

John W. Vinson[MVP]