Prev: Deleting Data in Sample database Northwind traders - stevie is pimping AGAIN!
Next: Quote or referral
From: Roger Bell on 2 Feb 2010 06:23
I have a report (Certificate) which I would like the date to convert from say
2.2.10 to 2nd February 2010. The Field on the Report is [Date of Request]

Could someone kindly reply with the correct syntax to include the formatting
in this text Box?

Also, if say the date is 3.2.10. can this be converted to 3rd February 2010,
ie can you tell Access to record 2nd, 3rd etc?

Many thanks for any help
From: Douglas J. Steele on 2 Feb 2010 07:43
Access doesn't have the ability to put the ordinal suffixes onto numbers.
You'll have to write your own function to do that, something like:

Function FormatOrdinalDate(InputValue As Variant) As String
Dim strDay As String

If IsDate(InputValue) Then
Select Case Day(InputValue)
Case 1, 21, 31
strDay = Day(InputValue) & "st"
Case 2, 22
strDay = Day(InputValue) & "nd"
Case 3, 23
strDay = Day(InputValue) & "rd"
Case Else
strDay = Day(InputValue) & "th"
End Select
FormatOrdinalDate = strDay & " " & _
Format(InputValue, "mmmm yyyy")
End If

End Function

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Roger Bell" <RogerBell(a)discussions.microsoft.com> wrote in message
news:B89EE124-AFD7-4D76-8959-A9C6CDEACAF5(a)microsoft.com...
>I have a report (Certificate) which I would like the date to convert from
>say
> 2.2.10 to 2nd February 2010. The Field on the Report is [Date of Request]
>
> Could someone kindly reply with the correct syntax to include the
> formatting
> in this text Box?
>
> Also, if say the date is 3.2.10. can this be converted to 3rd February
> 2010,
> ie can you tell Access to record 2nd, 3rd etc?
>
> Many thanks for any help


 | 
Pages: 1
Prev: Deleting Data in Sample database Northwind traders - stevie is pimping AGAIN!
Next: Quote or referral