How can i create 1st, 2nd, 3rd e.t.c numbers in Microsoft access when my numbers are appearing in text boxes ?
6 2594 Luuk 1,047
Recognized Expert Top Contributor
Someone solved this problem for you a while a go, and mentioned:
"Hope it's useful for someone.!"
i also post his code here: - Public Function IntToOrdinalString(MyNumber As Integer) As String
-
Dim sOutput As String
-
Dim iUnit As Integer
-
-
-
iUnit = MyNumber Mod 10
-
sOutput = ""
-
-
Select Case MyNumber
-
Case Is < 0
-
sOutput = ""
-
Case 10 To 19
-
sOutput = "th"
-
Case Else
-
Select Case iUnit
-
Case 0 'Zeroth only has a meaning when counts start with zero, which happens in a mathematical or computer science context.
-
sOutput = "th"
-
Case 1
-
sOutput = "st"
-
Case 2
-
sOutput = "nd"
-
Case 3
-
sOutput = "rd"
-
Case 4 To 9
-
sOutput = "th"
-
End Select
-
End Select
-
IntToOrdinalString = CStr(MyNumber) & sOutput
-
End Function
If you already have numbers (1, 2, 3..) in a column, then a simple solution can be.. just create a separate table wherein you can maintain two fields such as 'Number' and 'Ordinal Text' (numbers and their ordinal form-like 1st, 2nd etc.). Simply join these two tables in a query and you can get the desired result wherever you want.
Luuk 1,047
Recognized Expert Top Contributor
A user-defined function would be better to get this job donem because it's very static data we are taling about. - Function OrdinalSuffix(ByVal Num As Long) As String
-
Dim N As Long
-
Const cSfx = "stndrdthththththth" ' 2 char suffixes
-
N = Num Mod 100
-
If ((Abs(N) >= 10) And (Abs(N) <= 19)) _
-
Or ((Abs(N) Mod 10) = 0) Then
-
OrdinalSuffix = "th"
-
Else
-
OrdinalSuffix = Mid(cSfx, _
-
((Abs(N) Mod 10) * 2) - 1, 2)
-
End If
-
End Function
-
NeoPa 32,557
Recognized Expert Moderator MVP
Interesting solution by Luuk. I agree that's a far better way to handle numbers whose limits you don't know (How big would the table need to be to handle an infinite set of integers??)
While the code is also quite elegant it might be improved : - Private Const conSuffix As String = "stndrdthththththth"
-
-
Public Function OrdinalSuffix(ByVal lngVal As Long) As String
-
lngVal = Abs(lngVal) Mod 100
-
If (lngVal = 0) _
-
Or (lngVal Between 4 And 20) Then
-
OrdinalSuffix = "th"
-
Else
-
OrdinalSuffix = Mid(conSuffix, (lngVal Mod 10) * 2 - 1, 2)
-
End If
-
End Function
Luuk 1,047
Recognized Expert Top Contributor
I had to mention the fact that the code was found on the internet (^1).
But some guys change my posts and the delete the link... ;)
Removed link again. Link was to assign credit to Chip Pearson where the original code was found.
NeoPa 32,557
Recognized Expert Moderator MVP
Yes Luuk.
That guy was me and I've done it again as it's against the rules of the site to post a link to competing resources. It's called leeching and you'll find that's illegal on most forum sites you visit. As one of the moderators it's my responsibility to remove such links as and when I find them and direct the member posting them so they understand not to repeat the behaviour in future.
This is no reflection on Chip Pearson who provides a lot of good resources for Access, but I think most intelligent people will understand why leeching is unhelpful for a forum site to be able to do what it does - which ultimately is to help those who need help with Access.
Nevertheless, it was an example of some clever code and you did well to select and post it. We can all see that you attributed credit where it was due.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Adam |
last post by:
Hi,
I am trying to mark consective numbers in a data set and get a count as
to how many consecutive numbers exist in the a given line of data.
Here is an example line:
3, 5, 7, 9, 10, 13,...
|
by: Flavio |
last post by:
Hi,
I'm used in MS Access on Doing Queries based on Queries.
Now that I'm dealling a lot with large ammount of data I think this
procedure with an SQL Server back-end is falling a little bit...
|
by: Shailesh Humbad |
last post by:
I figured out what was causing the "Access is Denied" error when
calling functions from referenced DLLs in my service. I've tried to
be very detailed, so bear with me.
It turns out that...
|
by: Konrad Hammerer |
last post by:
Hi!
I have the following problem:
I have a query (a) using another query (b) to get the amount of records
of this other query (b), means:
select count(MNR) as Number from...
|
by: Manners |
last post by:
Hi! I am a VBA newbie, and cant seem to proceed past my current problem.
I have a large query, where I need to output a select query based on the parameter set by the do loop. The do loop is...
| |
by: abhishekdhal |
last post by:
I want to create a form which is linked to a table.I want to have an extra field "search" so that I can find the desired person...the search textfield will
have a corresponding button in it so that...
|
by: Cintury |
last post by:
I have the function that returns a numerical value according to its corresponding weekday in one column in the design view of access and what I do not know how to do is make this be "equal to" the...
|
by: kolly |
last post by:
I am developing a web application for a school that will allow them to register student and computed, and print students result. I am done with calculating the result but the only thing remain for me...
|
by: Linish Francis |
last post by:
In a program which adds two numbers of integer data type and prints the result, numbers greater than 32767 can neither be given as input nor obtained as output.
for eg:
if the operends are a and...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |