Rank 1st, 2th, 3rd, 4th

[mike wild]

I have a ranked list of numbers which when displayed in a text box via it's control source i would like to add the "th" or "nd" after 1 or 2, etc.
How is this done?

 

[WayneRyan]

Mike,

If the range of numbers is from 1 to 10, then this is
very easy to do (just store them in an array).

As the range of numbers gets larger, then some code
must be written.

Need more info ...

Wayne

 

[mike wild]

What i have done is create another table with two fields.
Id and suffix
Id has 200 records from 1 to 200.
And siffix carries the correct th, rd, etc for the number in the record.
And then made simply a select query with a join between Id field of the original table and the suffix field of the new table.

Nowever it is SLOW.

What is an array?

 

[WayneRyan]

Mike,

Dim aryNames(100) As String
aryNames(44) = "Joe"


However, I think this is better. I don't have Access
with me, so the syntax may be a bit off. Put this
in a Public Module:

' ***********************************************
Public Function Suffix (lngNum As Long) As String
Dim strNumber As String

strNumber = Str(lngNum)

select case Right(strNumber, 1)
Case "1"
strNumber = strNumber & "st"
Case "2"
strNumber = strNumber & "nd"
.
.
.
End Select
Suffix = strNumber
End Function
' ***********************************************

Wayne

 

[raskew]

Try this.

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

 

[mike wild]

thank you. I may stick with what i have - because well here is the code from the text boxes control source:
="You Are Currently In " & [rank] & [suffix] & " Place With £" & [total1] & " In The Bank"
and i don't know how to call the public procedure to work in this way.

BTW. Do you know how to get the focus to remain in the same place (same field) after a new record has been found. At the monent it just gose back to ID field (perhaps because it is tab stop 1)

 

[raskew]

="You Are Currently In '" & numsuffix([rank]) & "' Place With £" & [total1] & " In The Bank"

 

[mike wild]

raskew
You code has the wrong number of arguments error.

perhaps n=Right(mynum), 3)

i don't know
but it does not work.

 

[raskew]

In your select query, try adding this calculated field and using it as the source for your text box:

xx: "you are in " & numsuffix([rank]) & " place"

 

[mike wild]

Thank you raskew,
there was an underscore that needed removing and a space too.
All is working.
Thanks for you help and the code!!