How can i create 1st, 2nd, 3rd e.t.c numbers in Microsoft access when my numbers are appearing in text boxes ?
6 2600 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,568
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,568
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, 14
The relevant part of the script which currently checks for a
|
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 in
performance...
Some of the Queries are VERY complicated and I had to break it in
something like 5 or 6 queries...
|
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 libraries I made myself were not having this
problem, but it only happened with a zip library I downloaded and
copied into my program's directory. When using classes from this
library, my service threw an "Access Is Denied" FileLoadException,
which...
|
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 Jahrbuch_Einzelversand_Komplett (while
Jahrbuch_Einzelversand_Komplett is a query itself)
|
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 running through locations, where there will be many records per each location set in the parameter. The output needs to go to the same file location each time and then trigger a format macro in Excel.
The bit I am stuck with is taking the do loop...
| |
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 it can run a query which will show the result in the respective form fields related to the search.
Eg.
Form has 2 fields name ,id
in serch i want to enter the corresponding name to get the employee id...
I want the query to be specific...
|
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 number in the WeekdayName(number, , ) function.
My current code without the WeekdayName func:
SELECT Dnt.Date
, WEEKDAY(Dnt.Date) AS Date_weekday
, Dnt.FoodDonationsID
, Dnr.
, Dnt.Bakery
, Dnt.Dairy
|
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 to do is assign position for each students. the position I mean is first, second, third position and so on.
|
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 b and the result is c
neither a nor b nor c can be greater than 32767.now look at the below program
#include<iostream.h>
void main()
{
int a,b;
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |