Using the Switch Function

[SharonC]

This forum has been so helpful to me in the past that I thought I'd offer this tidbit of code that I discovered by myself while I was creating a report in Access 2000 in case anyone else has been stumped by this problem.

I'm creating a report that lists grade scores of students in descending value. I thought it'd be a nice touch to have "1st" for the top student, "2nd" for the second place holder etc listed by the student's name and grade. Of course [RecordCount] was a great help in numerating this for me, but I wanted the "st", "nd" and "rd" (etc.) to show up as well.

Then I came upon the problem of those falling in the 11th through 19th places...hmmmm.


By using the Switch function, I was able to put the following in the Expression builder of Access and it seems to work like a charm!

Just thought I'd post this, in case anyone else has this problem.

Again, thanks for discussion forum! Enjoy.

=Switch([RecordCount] Between 11 And 19,"th",Right([RecordCount],1)="0","th",Right([RecordCount],1)="1","st",Right([RecordCount],1)="2","nd",Right([RecordCount],1)="3","rd",Right([RecordCount],1)>="4","th")

 

[raskew]

You could, if you wanted, carry your work just a little further and come up with something like the following that provides the suffix for any number.

Function NumSuffix(MyNum As Variant) As String
'*******************************************
'Name:      NumSuffix (Function)
'Purpose:   Add suffix to an number
'Inputs:    ? NumSuffix(234)
'Output:    234th
'*******************************************

Dim n As Integer, x As Integer
Dim strSuf As String

n = Right(MyNum, 2)
x = n Mod 10
strSuf = Switch(n <> 11 And x = 1, "st", n <> 12 And x = 2, "nd", _
n <> 13 And x = 3, "rd", True, "th")
NumSuffix = LTrim(Str(MyNum)) & strSuf

End Function

 

[SharonC]

Thanks, Raskew! Yep! your idea is "cleaner, faster, better"!

This is definitely a "Why didn't I think of that?" moment...

Cheers