Combining Text and Date Vaules

[Guest]

I’m using the following expression with a date field. For a date of 01/01/05
it returns “This 1 day of January, 2005.†How can I get it to return "This
1st day..." (or 2nd day, 3rd day, etc.)?

LegalDate: "This " & Format([OrderDate],"d") & " day of " &
Format([OrderDate],"mmmm") & ", " & Format([OrderDate],"yyyy") & "."

 

[Douglas J. Steele]

You'll have to write your own function, along the lines of:

Function OrdinalDate(ValueIn As Long) As String

Select Case ValueIn
Case 1, 21, 31
OrdinalDate = ValueIn & "st"
Case 2, 22
OrdinalDate = ValueIn & "nd"
Case 3, 23
OrdinalDate = ValueIn & "rd"
Case Else
OrdinalDate = ValueIn & "th"
End Select

End Function

Change your query to

LegalDate: "This " & OrdinalDate(DatePart("d", [OrderDate])) & " day of " &
Format([OrderDate],"mmmm") & ", " & Format([OrderDate],"yyyy") & "."

 

[John Vinson]

I’m using the following expression with a date field. For a date of 01/01/05
it returns “This 1 day of January, 2005.” How can I get it to return "This
1st day..." (or 2nd day, 3rd day, etc.)?

LegalDate: "This " & Format([OrderDate],"d") & " day of " &
Format([OrderDate],"mmmm") & ", " & Format([OrderDate],"yyyy") & "."

I think you'll need a Switch() statement:

LegalDate: "This " & Format([OrderDate],"d") &
Switch(
Right(Format([OrderDate], "d"), 1) = 1, "st",
Right(Format([OrderDate], "d"), 1) = 2, "nd",
Right(Format([OrderDate], "d"), 1) = 3, "rd",
True, "th") &
" day of " &
Format([OrderDate],"mmmm") & ", " & Format([OrderDate],"yyyy") & "."

John W. Vinson[MVP]

 

[Douglas J. Steele]

I'm using the following expression with a date field. For a date of
01/01/05
it returns "This 1 day of January, 2005." How can I get it to return
"This
1st day..." (or 2nd day, 3rd day, etc.)?

LegalDate: "This " & Format([OrderDate],"d") & " day of " &
Format([OrderDate],"mmmm") & ", " & Format([OrderDate],"yyyy") & "."

I think you'll need a Switch() statement:

LegalDate: "This " & Format([OrderDate],"d") &
Switch(
Right(Format([OrderDate], "d"), 1) = 1, "st",
Right(Format([OrderDate], "d"), 1) = 2, "nd",
Right(Format([OrderDate], "d"), 1) = 3, "rd",
True, "th") &
" day of " &
Format([OrderDate],"mmmm") & ", " & Format([OrderDate],"yyyy") & "."

Won't that give you "This 11st day of ..."?

 

[John Vinson]

Won't that give you "This 11st day of ..."?

Oops! Yes indeed.

Your solution is obviously preferable (in that it will work).

John W. Vinson[MVP]