|
|
|
date: Fri, 04 Jul 2008 15:43:29 GMT,
group: microsoft.public.access.reports
back
Re: Automated way of adding suffix on dates? i.e. th, st,rd
For those that are interested!
=IIf(Day([Moved In])=1 Or Day([Moved In])=21 Or Day([Moved In])=31,"st",IIf
(Day([Moved In])=2 Or Day([Moved In])=22,"nd",IIf(Day([Moved In])=3 Or Day(
[Moved In])=23,"rd","th")))
Pascoe wrote:
>Thanks - and yes I appreciate your last point. So would you create a table
>with 1 - 31 suffix or write a function where the exception is catered for (I
>presume).
>
>Thanks!
>
>>No: you will have to write a function to achieve this.
>>
>[quoted text clipped - 7 lines]
>>> for
>>> say 4th July, so the "th" in this case, or 3rd November - "rd"?
--
Message posted via http://www.accessmonster.com
date: Fri, 04 Jul 2008 18:11:58 GMT
author: Pascoe via AccessMonster.com u43716@uwe
Re: Automated way of adding suffix on dates? i.e. th, st,rd
On Fri, 04 Jul 2008 15:43:29 GMT, Russell via AccessMonster.com wrote:
> Hi!
>
> Is there a way that Access will automatically add the suffix to a number for
> say 4th July, so the "th" in this case, or 3rd November - "rd"?
>
> Or should I add a table to look it up?
>
> Thanks!
> Russell.
You can adapt this to whatever format you wish.
Past the below function into a module:
Comment out and un-comment out, the appropriate part of the code for
the way you wish to display the data.
Watch out for word wrap on the longer lines.
Public Function DateOrdinalEnding(DateIn, MoIn As String)
' Will add an Ordinal ending to a date
' i.e. Novermber 13th, 2000
' MoIn determines Month Format, i.e. "mmm" for "Feb" or "mmmm" for
"February"
If IsNull(DateIn) Then
DateOrdinalEnding = ""
Exit Function
End If
Dim dteX As String
dteX = DatePart("d", DateIn)
dteX = dteX & Nz(Choose(IIf((Abs(dteX) Mod 100) \ 10 = 1, 0,
Abs(dteX)) Mod 10, "st", "nd", "rd"), "th")
' November 13th, 2000
DateOrdinalEnding = Format(DateIn, MoIn) & " " & dteX & ", " &
Format(DateIn, "yyyy")
' **********************
' 17th day of September 2003'
'DateOrdinalEnding = dteX & " day of " & Format(DateIn, " " & MoIn & "
yyyy")
' ***********************
' Friday 4th of July, 2008
' DateOrdinalEnding = Format(DateIn, "dddd") & " " & dteX & " of " &
Format(DateIn, " " & MoIn & ", yyyy")
' **********************
' "Day of MONTH, Year" 4th of JULY, 2008
' DateOrdinalEnding = dteX & " of " & UCase(Format(DateIn, " " & MoIn
& ", yyyy"))
End Function
__________________________
Then call it from a query:
NewDate:DateOrdinalEnding([DateField],"mmmm")
or from an unbound text control on a form or report:
=DateOrdinalEnding([DateField],"mmmm")
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
date: Fri, 4 Jul 2008 11:34:38 -0700
author: fredg lid
Re: Automated way of adding suffix on dates? i.e. th, st,rd
Fair enough
Suggestion: the IN operator might abbreviate that:
IIf(Day(Moved In]) IN (1, 21, 31), "st", ...
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Pascoe via AccessMonster.com" <u43716@uwe> wrote in message
news:86a7ba7c2dd3c@uwe...
> For those that are interested!
>
> =IIf(Day([Moved In])=1 Or Day([Moved In])=21 Or Day([Moved
> In])=31,"st",IIf
> (Day([Moved In])=2 Or Day([Moved In])=22,"nd",IIf(Day([Moved In])=3 Or
> Day(
> [Moved In])=23,"rd","th")))
>
> Pascoe wrote:
>>Thanks - and yes I appreciate your last point. So would you create a table
>>with 1 - 31 suffix or write a function where the exception is catered for
>>(I
>>presume).
>>
>>Thanks!
>>
>>>No: you will have to write a function to achieve this.
>>>
>>[quoted text clipped - 7 lines]
>>>> for
>>>> say 4th July, so the "th" in this case, or 3rd November - "rd"?
date: Sat, 5 Jul 2008 12:38:22 +0800
author: Allen Browne lid
|
|
