Date suffix using Case method 2

[scottian]

Can anyone point me in the right direction with this.
I trying to add the suffix (st, nd, rd etc) to a date. but not getting the right result. Im trying to use Case to get the right suffix, if i just verify 1st, 2nd and 3rd of the month it works but obviously i need to also pick out 21st, 22nd,23rd and 31st. So this is what im trying but it doesnt get the 1, 2 and 3 it just goes straight to 'th'

Private Sub Report_Activate()
Dim SuffixNum As Integer
Dim SuffTemp As String

SuffixNum = Format(Me.[Datestop], "d")
MsgBox SuffixNum

Select Case SuffixNum

Case 1 Or 21 Or 31
SuffTemp = "st"

Case 2 Or 22
SuffTemp = "nd"

Case 3 Or 23
SuffTemp = "rd"

Case Else
SuffTemp = "th"

End Select
Me.[Text55] = SuffixNum & SuffTemp
End Sub

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[AlexCuse]

Here's an easy function to return the suffix for a given day number:

Function EzSuffix(ByVal DateNum As Integer) As String

    Dim Ar() As String
    Ar = Split("st,nd,rd,th,th,th,th,th,th,th," & _
               "th,th,th,th,th,th,th,th,th,th," & _
               "st,nd,rd,th,th,th,th,th,th,th,st", ",")
    
    EzSuffix = Ar(DateNum - 1)
End Function

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Ps - have you tried this:

Me.[Text55] = [b]CStr([/b]SuffixNum[b])[/b] & SuffTemp

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[SgtJarrow]

Nice little function Alex. Simple and concise. Star for you.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[scottian]

I cant seem to work this out, stumbling in the dark.

How do i get the string EzSuffix into the report? The function doesnt work if i paste it into the report code window. So Ive put it into its own module. But i cant work out how to get the value EzSuffix into the report.

Ive also tried - Me.[Text55] = CStr(SuffixNum) & SuffTemp
But im still getting 1th, 2th and 3th.
Thanks for the help Alex.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[AlexCuse]

Hey scott. What version of Access are you using?

I'm using 2003, and that function is kept in a module called DateFunctions (I know, real creative ;-) )

I set the text box's control source to this expression:

=[DateNum] & EzSuffix([DateNum])

and it works alright.

Let me know if this works out for you.

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[scottian]

I suppose this makes a difference, Im using A97.
Im now getting an error on the 'Split', 'sub or function not defined'.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[AlexCuse]

Yikes! I have never used access 97, but it must not have the split function. Try this guy out, have to use a variant declaration and some other stuff I kind of dislike, but maybe it will work (no split function used)

If this doesn't work, maybe we need to create a second method to fill the array one at a time. Oh joy!

Function EzSuffixNoSplit(ByVal DateNum As Integer) As String

    Dim Ar
    Ar = Array("st", "nd", "rd", "th", "th", _
                            "th", "th", "th", "th", "th", _
                            "th", "th", "th", "th", "th", _
                            "th", "th", "th", "th", "th", _
                            "st", "nd", "rd", "th", "th", "th", _
                            "th", "th", "th", "th", "st")
    
    EzSuffixNoSplit = Ar(DateNum - 1)
End Function

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[scottian]

You absolute diamond.....thank you.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks