Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2002-04-10
10
Medium Priority
?
1,220 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 200 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

618 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