date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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.
Click to expand...

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]
 
Back
Top