Solved

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

Posted on 2002-04-10
10
1,172 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6931445
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
ID: 6931450
Oops,

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

Nic;o)
0
 
LVL 18

Expert Comment

by:deighton
ID: 6931607
SELECT [date], cstr(day([date]))+mid("stndrdthththththththththththththththththstndrdthththththththst",(day([date])-1)*2+1,2)
FROM billing
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 18

Expert Comment

by:deighton
ID: 6931687
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
ID: 6931717
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
 

Author Comment

by:kkan
ID: 6932300
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
ID: 6932304
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
ID: 6932344
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
ID: 6933384
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
ID: 6995460
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

705 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