Number format into ordinal number (1st, 2nd, 3rd, 4th) displayed on form | access VBA (1 Viewer)

[Ihk]

Table1
I have a table with a number field.
Form1
I have data entry form with that control bound to that table. This control is hidden on form, but has DMAX before update. So numbers keep increasing, while user enter data in the table via that form.
Form2
Now I want these numbers to be displayed on another form as ordinal number format like 1st, 2nd, 3rd, 4th etc instead of 1,2,3,4.
How can I get that?
thanks

 

[plog]

Now I want these numbers to be stored as ordinal number format like 1st, 2nd, 3rd, 4th etc instead of 1,2,3,4.

I don't think you do. I bet you want to display them in ordinal format. Big difference.

You change it to a text field and store "5th" and it gets much more difficult to calculate the next number. Right now you use math to grab the biggest number in the database, use math to add one. With a text field, math goes away. Stored as text, "9th" is after "10th". Your system breaks after 10 positions. And that's not even mentioning having to strip the "th (or possibly the "st", "nd" or "rd")" off each number so that you can determine what to add one to, followed by adding the "th" (or possibly the "st", "nd" or "rd") back to that new number.

Keep it as a number and use a function to display that number as you wish.

 

[Ihk]

I don't think you do. I bet you want to display them in ordinal format. Big difference.

You change it to a text field and store "5th" and it gets much more difficult to calculate the next number. Right now you use math to grab the biggest number in the database, use math to add one. With a text field, math goes away. Stored as text, "9th" is after "10th". Your system breaks after 10 positions. And that's not even mentioning having to strip the "th (or possibly the "st", "nd" or "rd")" off each number so that you can determine what to add one to, followed by adding the "th" (or possibly the "st", "nd" or "rd") back to that new number.

Keep it as a number and use a function to display that number as you wish.

You are right and I agree, want to display.
Because i want to have positions or ranks.
So I am going edit my original post.
Thanks for pointing out.

 

[plog]

I provided that answer as well--you build a function. You pass it a number, it returns a string of it correctly formatted. I am sure if you search the forum that code has been written and posted on here before (although its pretty simple to roll your own and would be a great introduction to VBA programming.)

 

[NauticalGent]

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

 

[gemma-the-husky]

fwiw, you would think there would be a format setting to display a number in it's ordinal form, wouldn't you? (Edit. But there isn't)