• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1259
  • Last Modified:

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

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
kkan
Asked:
kkan
  • 3
  • 3
  • 3
  • +1
1 Solution
 
nico5038Commented:
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
 
nico5038Commented:
Oops,

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

Nic;o)
0
 
deightonCommented:
SELECT [date], cstr(day([date]))+mid("stndrdthththththththththththththththththstndrdthththththththst",(day([date])-1)*2+1,2)
FROM billing
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
deightonCommented:
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
 
YoinkCommented:
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
 
kkanAuthor Commented:
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
 
kkanAuthor Commented:
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
 
nico5038Commented:
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
 
deightonCommented:
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
 
kkanAuthor Commented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now