Displaying: date: 1st, 2nd, 3rd, 11th, 21st 
Author Message
 Displaying: date: 1st, 2nd, 3rd, 11th, 21st

The subject describes what i need to make access do with
displaying the date, is there a formatting code for this?

Or do i need to do a little code



Mon, 17 May 2004 21:38:58 GMT  
 Displaying: date: 1st, 2nd, 3rd, 11th, 21st
Bob,
Place this in a Module:

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" = "Feb" or "mmmm" =
"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")

DateOrdinalEnding = Format(DateIn, MoIn) & " " & dteX & ", " &
Format(DateIn, "yyyy")

End Function
====

Then in a Form or Report control:
= DateOrdinalEnding([ADate],"mmmm")

Will display today's date as November 29th, 2001
--
Fred

Please reply only through this newsgroup.
I do not reply to direct e-mails.


Quote:
> The subject describes what i need to make access do with
> displaying the date, is there a formatting code for this?

> Or do i need to do a little code



Tue, 18 May 2004 00:21:04 GMT  
 Displaying: date: 1st, 2nd, 3rd, 11th, 21st

Quote:

>The subject describes what i need to make access do with
>displaying the date, is there a formatting code for this?

>Or do i need to do a little code

Bob

There isn't a format that you can create that will do this. I use a short VBA
function to do this:

Function fCardinalDate(dtmDate As Date) As String
    Select Case Day(dtmDate)
        Case 1,21,31
            fCardinalDate=Day(dtmDate) & "st" & Format(dtmDate,"mmmm") & " " &
Year(dtmDate)
        Case 2,22
            fCardinalDate=Day(dtmDate) & "nd" & Format(dtmDate,"mmmm") & " " &
Year(dtmDate)
        Case 3,23
            fCardinalDate=Day(dtmDate) & "rd" & Format(dtmDate,"mmmm") & " " &
Year(dtmDate)
        Case Else
            fCardinalDate=Day(dtmDate) & "th" & Format(dtmDate,"mmmm") & " " &
Year(dtmDate)
    End Select
End Sub

This function will return the date in the format '1st January 2001'. Obviously,
if you want the date in a slightly different format, then you will need to
alter the function as required.

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring - http://a.webring.com/hub?ring=microsoftaccess



Tue, 18 May 2004 13:51:16 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Displaying Day as 1st, 2nd, 3rd, etc.

2. Return 1st,2nd,3rd,4th from Date

3. Get 1st, 2nd, and 3rd Letter From a String

4. 1st box<2nd box<3rd box?

5. 1st month, 2nd month, 3rd month...

6. If 1st cell is empty, I want data from 2nd cell to copy into 3rd cell

7. Postions - 1st 2nd 3rd 4th etc

8. Search 1st field and display corresponding 2nd field....

9. Displaying the 2nd or 3rd worksheet on web pages

10. Triggering 2nd show on 2nd PC from 1st show


 
Powered by phpBB® Forum Software © phpBB Group