Solved

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

Posted on 2002-04-10
10
1,165 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Form performance table vs. query 27 61
MS Access query 16 52
Handle Apostrophes in SQL Parameter 16 59
ACCESS / VBA - Count of Rows in Table 4 32
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.

740 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