Solved

The "st" , "nd", "rd" and "th" in 1st, 2nd 3rd and 4th

Posted on 2002-04-10
10
1,129 Views
Last Modified: 2010-08-05
Hello everyone,

I have seperated the DAY in a date in a query using: Day: DatePart("d",[Date])

How do I get Access to include the appropriate suffix "st" , "nd", "rd" and "th" in 1st, 2nd 3rd and 4th
all the way up to 31st?

Thanks

kan
0
Comment
Question by:kkan
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I guess the only way will be to create your own function that translates an integer into a string with the right suffix.
Normally The FORMAT function would do so, but haven't found it.

You'll need to code a module like:

FUNCTION fncDaySuffix(intDay as integer) as String
dim strSuffix as String

SELECT CASE intDay
CASE 1 11 21 31
     strSuffix = "st"
CASE 2 12 22
     strSuffix = "nd"
etc...

END SELECT

fncDaySuffix = intDay & strSuffix

END FUNCTION

Now in the query you can use:
DaySuf:fncDaySuffix(intDayField)

Clear ?

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Oops,

CASE 1 11 21 31
should be:
CASE 1, 11, 21, 31

Nic;o)
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
SELECT [date], cstr(day([date]))+mid("stndrdthththththththththththththththththstndrdthththththththst",(day([date])-1)*2+1,2)
FROM billing
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
the
mid("stndrdthththththththththththththththththstndrdthththththththst",(day([date])-1)*2+1,2)

getting the st, nd etc for you,


note that I used the day function rather that 'datepart', datepart is probably more up to date, but that's just the way I am.
0
 
LVL 2

Accepted Solution

by:
Yoink earned 50 total points
Comment Utility
I have used this to populate a field on a report called txtSuffix(it is not as pretty as Nico's, but it works):

If DatePart("d", Date) = 1 Then
Me.txtSuffix = "st"
ElseIf DatePart("d", Date) = 2 Then
Me.txtSuffix = "nd"
ElseIf DatePart("d", Date) = 3 Then
Me.txtSuffix = "rd"
ElseIf DatePart("d", Date) = 21 Then
Me.txtSuffix = "st"
ElseIf DatePart("d", Date) = 22 Then
Me.txtSuffix = "nd"
ElseIf DatePart("d", Date) = 23 Then
Me.txtSuffix = "rd"
ElseIf DatePart("d", Date) = 31 Then
Me.txtSuffix = "st"
Else
Me.txtSuffix = "th"
End If

Using the below syntax, I can format a date to appear as  The 10th day of April, 2002.:  
"The " & Format(Date(),"d") & "" & [txtSuffix] & " day of " & Format(Date(),"mmmm"", ""yyyy") & "."

Just another option.   {:>)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:kkan
Comment Utility
Thank you all for your excellent comments.

Nic;o -  I am always very eager to learn new approaches. Your examples are always so concentrated (sophisticated code) that I usually end up learning more than the question I asked. Sometimes I end up using a longer approach (from another comment) that uses code that I am already familiar with, simply because my end goal is to get the job done as quickly as possible without having to ask  you to perform the tedious chore of elaborating on the parts of the code/method you use that I am not familiar with if I can't figureit out myself.

Yoink. - your code is very much what I had in mind (easy to understand what you're doing)

I will be attempting to apply them to my report over  the course of the next day or so and allocate the points according to which one works best for me.

Thanks again everyone.

kan
0
 

Author Comment

by:kkan
Comment Utility
deighton,

Your solution looks really concise and sleek. I am just not at a level to understand what the heck you are doing.

Thnaks nonetheless

kan
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
kan,

The code of deighton is using the daynumber to extract the needed suffix from a string where you have the needed suffixes all "concatenated".
By using the daynumber * 2 you get the startingpoint in the string to extract the two characters you need. The string itself is just a concatenation of all suffixes.
It's a nice solution that will be rather fast.

The function is doing exactly the same task and is the way I normally go as it's easier to change afterwards e.g. when you want iso "1st" a "1 st" all you need is to add a space in the individual strings or in the final assignment of the daynumber with the strSuffix. In the case of deighton you'll need to change the string and the multiplyer like:
cstr(day([date]))+mid(" st nd rd th th th th th th th th th th th th th th th th th st nd rd th th th th th th th st",(day([date])-1)*3+1,2)

But just give it a try ;-)

Nic;o)  
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
mid(x,y,z) goes to character number y of string z, then gets z characters

so

mid("fred",2,2) starts at r and ends at e giving 're'

I used this function to look up the suffix needed form a string based on the day number.

0
 

Author Comment

by:kkan
Comment Utility
Thank you everyone for your comments

I used Yoinks solution b/c I understand it best and it works in my situation.

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now