Suffix Help

[JMZ]

I have a report that show what place a player finished at, this text box will show 1, 2, 3 and I would like it to show 1st, 2nd, 3rd.
Does anyone have an idea on how to.

 

[MajP]

I would make a quick table
NumberID ' numeric
NumberDesc 'text

1 1st
2 2nd
3 3rd
4 4th
....

Then in your query link your field to NumberID and display NumberDesc.

 

[MajP]

There are also functions that do this, but the above solution should take a few minutes and faster then finding code which would be less efficient anyways. You can do an iif or Switch in a query too.

Switch Function - Microsoft Support

Use the Switch function in Acess to evaluate a list of expressions.

support.microsoft.com

The example is very similar

 

[CJ_London]

Or the choose function

choose(num,”1st”,”2nd”,”3rd”,”4th”…..)

 

[JMZ]

CJ
Can you explain how, I'm just getting into programing.

 

[Gasman]

Just as CJ_London has posted.
In the control source put

=choose(num,”1st”,”2nd”,”3rd”)

where num is the name of the field holding your 1, 2 or 3 value

 

[JMZ]

It wants me to enter paramenter value "1st"

 

[Gasman]

Show what you have written.

tt=1
? choose(tt,"1st","2nd","3rd")
1st
tt=2
? choose(tt,"1st","2nd","3rd")
2nd
tt=3
? choose(tt,"1st","2nd","3rd")
3rd

 

[JMZ]

=Choose([placed],[”1st”],[”2nd”],[”3rd”])

 

[GPGeorge]

It wants me to enter paramenter value "1st"

One of the most important things we have to learn about getting help in a technical forum, is that only you, the person asking the question, can actually see the tables, forms, code etc. in your application.

Others can make educated guesses about what might be going on, but without being able to see directly, it is always just a guess.

The solution is quite direct and easy to implement.

• Show the forum what you see.
• Tell the forum what you expect to happen.
• Tell the forum what actually does happen.

 

[MajP]

This function should work for all numbers.

Public Function GetOrdinal(TheNumber As Variant) As String
  Dim suffix As String
  If Not IsNull(TheNumber) Then
    suffix = "th"
    If TheNumber Mod 100 >= 11 And TheNumber Mod 100 <= 13 Then
      suffix = "th"
    ElseIf TheNumber Mod 10 = 1 Then
      suffix = "st"
    ElseIf TheNumber Mod 10 = 2 Then
      suffix = "nd"
    ElseIf TheNumber Mod 10 = 3 Then
     suffix = "rd"
    End If
    GetOrdinal = CStr(TheNumber) & suffix
  End If
End Function

you call it like = GetOrdinal([placed])
I demoed a helper table too.

With the helper table you can do the second column too which you cannot write a simple function for.

That function can be rewritten and even done inline.

number to ordinal list (1st, 2nd, 3rd...)

Hi All, Here's a quick and nasty function to convert a number to it's ordinal representation. e.g. 1 = 1st 2 = 2nd 3 = 3rd etc. Public Function IntToOrdinalString(MyNumber As Integer) As String Dim sOutput As String Dim iUnit As Integer iUnit = MyNumber Mod 10...

www.access-programmers.co.uk

 

[GPGeorge]

=Choose([placed],[”1st”],[”2nd”],[”3rd”])

Notice any difference between what you posted and what gasman posted?

 

[JMZ]

GPGeorge​

I think I've done that, go to the start of the thread.

 

[JMZ]

MajP
It worked like a charm. I will learn more from this code, thank you.

 

[JMZ]

GPGeorge
I did copy what Gasman posted, when I placed it in the control source of the text box the code was change automatically to what you just posted. Quit being so quick to judge.

 

[MajP]

@JMZ
You have smart quotes or typographer quotes around your strings. This is probably because you copied this out of Word or some other document. You cannot type that directly into Access AFAIK. Because of that, Access assumed it was an invalid string and automatically put the [] as if it was a field. You cannot use smart quotes in sql or vba only straight quotes.

Straight and curly quotes | Typography for Lawyers

Typography for Lawyers

typographyforlawyers.com

I tried with the smart quotes and it failed, but Gasman's code worked with straight quotes.

 

[Gasman]

GPGeorge
I did copy what Gasman posted, when I placed it in the control source of the text box the code was change automatically to what you just posted. Quit being so quick to judge.

Not what I posted?

Edit: If you have to copy, paste,modify and copy again, then do the initial paste to notepad.

 

[MajP]

IF the OP pastes

“1st”

not

 "1st"

it will automatically do this

[“1st”]

and will not run.

 

[JMZ]

I did not know that I copied from here and pasted it in notepad and change num to placed, copied and pasted it in the control source where access changed it, so I didn't question it. My apologies GPGeorge for being rude.

 

[Gasman]

Well done for the apology.
It takes courage to admit when you are wrong.
However, if you used notepad, I am puzzled as to how that occurred.

Ah, I see why now, you copied what I copied from @CJ_London post in post #5, and that had the smart quotes, so I was responsible for that, my apologies for that.

Note to self, always use code tags for posting code, but inspect that which came from non tagged code.