Date Format

[fel]

I just do not know which category of Access does this falls under. Anyway,
my question is how should I format my date in such a way that it shows only
the day? Example - if I key in 31-01-2006, I will want it to reflect on my
report as 31st. Thanks.

 

[Tom Wickerath]

You can use the Day function to return the day of the date. Try the following
query in the sample Northwind database:

SELECT Orders.ShippedDate, Day([ShippedDate]) AS DayOfMonth
FROM Orders;

This won't get you the suffixes, such as "rd" (as in 3rd) or "st" (as in
31st), but it's a start. You could write a custom function to return the
appropriate suffix.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[fel]

Thanks Tom for your reply. I have no problems in getting the day back only.
I am just left with the portion of getting the "st" an "rd" out for my
report. You were saying that I can write a function in getting the suffix
out. Will you be able to help me on this?

Fel

You can use the Day function to return the day of the date. Try the following
query in the sample Northwind database:

SELECT Orders.ShippedDate, Day([ShippedDate]) AS DayOfMonth
FROM Orders;

This won't get you the suffixes, such as "rd" (as in 3rd) or "st" (as in
31st), but it's a start. You could write a custom function to return the
appropriate suffix.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I just do not know which category of Access does this falls under. Anyway,
my question is how should I format my date in such a way that it shows only
the day? Example - if I key in 31-01-2006, I will want it to reflect on my
report as 31st. Thanks.

 

[Tom Wickerath]

How about this for the same example, using the Northwind sample database?
(courtesy of a previous answer by Vanderghast, Access MVP):

SELECT Orders.ShippedDate, Day([ShippedDate]) &
Nz(Choose(1+Day([ShippedDate]) Mod 10,"th","st","nd","rd"),"th")
AS DayOfMonth
FROM Orders;


Reference:
http://groups.google.com/group/micr..._frm/thread/c7bafb377616a111/fd2288c9cc6607ea

Google Groups Advanced search is your best friend...


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom for your reply. I have no problems in getting the day back only.
I am just left with the portion of getting the "st" an "rd" out for my
report. You were saying that I can write a function in getting the suffix
out. Will you be able to help me on this?

Fel

You can use the Day function to return the day of the date. Try the following
query in the sample Northwind database:

SELECT Orders.ShippedDate, Day([ShippedDate]) AS DayOfMonth
FROM Orders;

This won't get you the suffixes, such as "rd" (as in 3rd) or "st" (as in
31st), but it's a start. You could write a custom function to return the
appropriate suffix.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I just do not know which category of Access does this falls under. Anyway,
my question is how should I format my date in such a way that it shows only
the day? Example - if I key in 31-01-2006, I will want it to reflect on my
report as 31st. Thanks.

 

[Tom Wickerath]

Oops....it looks like there are some inconsistencies in the referenced method
I provided. For example, a shipped date of 12-Jul-1996 shows 12nd, and
11-Jul-1996 shows 11st. So, maybe try this function provided by Access MVP
Dirk Goldgar and see if it does better:

http://groups.google.com/group/micr..._frm/thread/763d13b33ad20179/5eaa24e71b4ecd34
Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

How about this for the same example, using the Northwind sample database?
(courtesy of a previous answer by Vanderghast, Access MVP):

SELECT Orders.ShippedDate, Day([ShippedDate]) &
Nz(Choose(1+Day([ShippedDate]) Mod 10,"th","st","nd","rd"),"th")
AS DayOfMonth
FROM Orders;


Reference:
http://groups.google.com/group/micr..._frm/thread/c7bafb377616a111/fd2288c9cc6607ea

Google Groups Advanced search is your best friend...


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom for your reply. I have no problems in getting the day back only.
I am just left with the portion of getting the "st" an "rd" out for my
report. You were saying that I can write a function in getting the suffix
out. Will you be able to help me on this?

Fel

You can use the Day function to return the day of the date. Try the following
query in the sample Northwind database:

SELECT Orders.ShippedDate, Day([ShippedDate]) AS DayOfMonth
FROM Orders;

This won't get you the suffixes, such as "rd" (as in 3rd) or "st" (as in
31st), but it's a start. You could write a custom function to return the
appropriate suffix.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

I just do not know which category of Access does this falls under. Anyway,
my question is how should I format my date in such a way that it shows only
the day? Example - if I key in 31-01-2006, I will want it to reflect on my
report as 31st. Thanks.

 

[fel]

Tom, the one that you had given me is using the SQL. I tried using the query
to get the results but only returned with "th" . Will you be able to give me
in query based? Thanks.

Fel

How about this for the same example, using the Northwind sample database?
(courtesy of a previous answer by Vanderghast, Access MVP):

SELECT Orders.ShippedDate, Day([ShippedDate]) &
Nz(Choose(1+Day([ShippedDate]) Mod 10,"th","st","nd","rd"),"th")
AS DayOfMonth
FROM Orders;


Reference:
http://groups.google.com/group/micr..._frm/thread/c7bafb377616a111/fd2288c9cc6607ea

Google Groups Advanced search is your best friend...


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom for your reply. I have no problems in getting the day back only.
I am just left with the portion of getting the "st" an "rd" out for my
report. You were saying that I can write a function in getting the suffix
out. Will you be able to help me on this?

Fel

You can use the Day function to return the day of the date. Try the following
query in the sample Northwind database:

SELECT Orders.ShippedDate, Day([ShippedDate]) AS DayOfMonth
FROM Orders;

This won't get you the suffixes, such as "rd" (as in 3rd) or "st" (as in
31st), but it's a start. You could write a custom function to return the
appropriate suffix.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

I just do not know which category of Access does this falls under. Anyway,
my question is how should I format my date in such a way that it shows only
the day? Example - if I key in 31-01-2006, I will want it to reflect on my
report as 31st. Thanks.

 

[fel]

Tom, I had it figured out in query based. It works perfectly well. Lots of
thanks.

Fel

How about this for the same example, using the Northwind sample database?
(courtesy of a previous answer by Vanderghast, Access MVP):

SELECT Orders.ShippedDate, Day([ShippedDate]) &
Nz(Choose(1+Day([ShippedDate]) Mod 10,"th","st","nd","rd"),"th")
AS DayOfMonth
FROM Orders;


Reference:
http://groups.google.com/group/micr..._frm/thread/c7bafb377616a111/fd2288c9cc6607ea

Google Groups Advanced search is your best friend...


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom for your reply. I have no problems in getting the day back only.
I am just left with the portion of getting the "st" an "rd" out for my
report. You were saying that I can write a function in getting the suffix
out. Will you be able to help me on this?

Fel

You can use the Day function to return the day of the date. Try the following
query in the sample Northwind database:

SELECT Orders.ShippedDate, Day([ShippedDate]) AS DayOfMonth
FROM Orders;

This won't get you the suffixes, such as "rd" (as in 3rd) or "st" (as in
31st), but it's a start. You could write a custom function to return the
appropriate suffix.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

I just do not know which category of Access does this falls under. Anyway,
my question is how should I format my date in such a way that it shows only
the day? Example - if I key in 31-01-2006, I will want it to reflect on my
report as 31st. Thanks.

 

[Tom Wickerath]

Hi Fel,

I'm not sure what I'm doing wrong, because I can't seem to get the
query-only based method to work for the 11th, 12th and 13th of the month. I
just modified the function that Dirk had provided in the earlier post. Create
a table named Orders with a field named ShippedDate. Enter one record for
each day of the month, for, say, July (which has 31 days in the month). Add
the function shown below to a new module. Name the module something like
basDateOrdinal (ie. use a different name from the name of the function). Then
copy and paste this query into the SQL window of a new query:

SELECT Orders.ShippedDate,
Day([ShippedDate]) & fncOrdinal([ShippedDate])
AS DayOfMonthFunction,
Day([ShippedDate]) & Nz(Choose(1+Day([ShippedDate]) Mod
10,"th","st","nd","rd"),"th")
AS DayOfMonthSQL
FROM Orders
ORDER BY Orders.ShippedDate;

This query provides a side-by-side comparison of the two methods.

Here is the modified function that goes into a new module:

Option Compare Database
Option Explicit

Function fncOrdinal(ByVal dteDate As Date) As String

Dim intNumber As Integer
intNumber = Day(dteDate)


Select Case Right$(intNumber, 1)
Case "1"
If Right$(intNumber, 2) = "11" Then
fncOrdinal = "th"
Else
fncOrdinal = "st"
End If
Case "2"
If Right$(intNumber, 2) = "12" Then
fncOrdinal = "th"
Else
fncOrdinal = "nd"
End If
Case "3"
If Right$(intNumber, 2) = "13" Then
fncOrdinal = "th"
Else
fncOrdinal = "rd"
End If
Case Else
fncOrdinal = "th"
End Select


End Function

'********************End Code**************

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[fel]

Oh yeah, I just realized it too. The link that you had offered to me, may I
know where am I supposed to input in? I am a bit lost.

Fel

Oops....it looks like there are some inconsistencies in the referenced method
I provided. For example, a shipped date of 12-Jul-1996 shows 12nd, and
11-Jul-1996 shows 11st. So, maybe try this function provided by Access MVP
Dirk Goldgar and see if it does better:

http://groups.google.com/group/micr..._frm/thread/763d13b33ad20179/5eaa24e71b4ecd34
Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

How about this for the same example, using the Northwind sample database?
(courtesy of a previous answer by Vanderghast, Access MVP):

SELECT Orders.ShippedDate, Day([ShippedDate]) &
Nz(Choose(1+Day([ShippedDate]) Mod 10,"th","st","nd","rd"),"th")
AS DayOfMonth
FROM Orders;


Reference:
http://groups.google.com/group/micr..._frm/thread/c7bafb377616a111/fd2288c9cc6607ea

Google Groups Advanced search is your best friend...


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom for your reply. I have no problems in getting the day back only.
I am just left with the portion of getting the "st" an "rd" out for my
report. You were saying that I can write a function in getting the suffix
out. Will you be able to help me on this?

Fel

:

You can use the Day function to return the day of the date. Try the following
query in the sample Northwind database:

SELECT Orders.ShippedDate, Day([ShippedDate]) AS DayOfMonth
FROM Orders;

This won't get you the suffixes, such as "rd" (as in 3rd) or "st" (as in
31st), but it's a start. You could write a custom function to return the
appropriate suffix.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

I just do not know which category of Access does this falls under. Anyway,
my question is how should I format my date in such a way that it shows only
the day? Example - if I key in 31-01-2006, I will want it to reflect on my
report as 31st. Thanks.

 

[Tom Wickerath]

***Repost--I don't see a copy of a reply I sent earlier***

Hi Fel,

I'm not sure what I'm doing wrong, but I can't get the query-only based
method to work, specifically for the 11th, 12th and 13th of the month. I made
some modifications to the function that I referenced earlier, which are shown
below. Try the following as an experiment:

1.) Create a table named Orders, with a field named ShippedDate.

2.) Add one record for each day of the month, for a month that includes 31
days, such as July. Just for fun, add one more record that does not include a
date in the ShippedDate field (null).

3.) Copy the following SQL statement and paste it into a new query:

SELECT Orders.ShippedDate,
Day([ShippedDate]) & fncOrdinal([ShippedDate])
AS DayOfMonthFunction,
Day([ShippedDate]) & Nz(Choose(1+Day([ShippedDate]) Mod
10,"th","st","nd","rd"),"th")
AS DayOfMonthSQL,
IIf(Day([ShippedDate]) & fncOrdinal([ShippedDate])=Day([ShippedDate]) &
Nz(Choose(1+Day([ShippedDate]) Mod 10,"th","st","nd","rd"),"th"),"Yes","NO")
AS [Are They Equal?]
FROM Orders
ORDER BY Orders.ShippedDate;

4.) Copy the following function into a new module. Name the module something
like basDateOrdinal (just don't name it the same as the function):

'*******************Begin Code*********************
Option Compare Database
Option Explicit

Function fncOrdinal(ByVal varDate As Variant) As String

Dim intNumber As Integer

If Not IsNull(varDate) Then
intNumber = Day(varDate)

Select Case Right$(intNumber, 1)
Case "1"
If Right$(intNumber, 2) = "11" Then
fncOrdinal = "th"
Else
fncOrdinal = "st"
End If
Case "2"
If Right$(intNumber, 2) = "12" Then
fncOrdinal = "th"
Else
fncOrdinal = "nd"
End If
Case "3"
If Right$(intNumber, 2) = "13" Then
fncOrdinal = "th"
Else
fncOrdinal = "rd"
End If
Case Else
fncOrdinal = "th"
End Select
End If

End Function

'*******************End Code**********************

5.) Run the query. I think you will see how the SQL only solution leaves
something to be desired, especially for the 11, 12 and 13th of the month.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[fel]

Tom, really appreciate your asisstance. But I still can't get the desired
results. Perhaps I will have to rephrase the sentence structure which don't
require me to have a suffix behind the dates.

Thanks anyway, Fel

***Repost--I don't see a copy of a reply I sent earlier***

Hi Fel,

I'm not sure what I'm doing wrong, but I can't get the query-only based
method to work, specifically for the 11th, 12th and 13th of the month. I made
some modifications to the function that I referenced earlier, which are shown
below. Try the following as an experiment:

1.) Create a table named Orders, with a field named ShippedDate.

2.) Add one record for each day of the month, for a month that includes 31
days, such as July. Just for fun, add one more record that does not include a
date in the ShippedDate field (null).

3.) Copy the following SQL statement and paste it into a new query:

SELECT Orders.ShippedDate,
Day([ShippedDate]) & fncOrdinal([ShippedDate])
AS DayOfMonthFunction,
Day([ShippedDate]) & Nz(Choose(1+Day([ShippedDate]) Mod
10,"th","st","nd","rd"),"th")
AS DayOfMonthSQL,
IIf(Day([ShippedDate]) & fncOrdinal([ShippedDate])=Day([ShippedDate]) &
Nz(Choose(1+Day([ShippedDate]) Mod 10,"th","st","nd","rd"),"th"),"Yes","NO")
AS [Are They Equal?]
FROM Orders
ORDER BY Orders.ShippedDate;

4.) Copy the following function into a new module. Name the module something
like basDateOrdinal (just don't name it the same as the function):

'*******************Begin Code*********************
Option Compare Database
Option Explicit

Function fncOrdinal(ByVal varDate As Variant) As String

Dim intNumber As Integer

If Not IsNull(varDate) Then
intNumber = Day(varDate)

Select Case Right$(intNumber, 1)
Case "1"
If Right$(intNumber, 2) = "11" Then
fncOrdinal = "th"
Else
fncOrdinal = "st"
End If
Case "2"
If Right$(intNumber, 2) = "12" Then
fncOrdinal = "th"
Else
fncOrdinal = "nd"
End If
Case "3"
If Right$(intNumber, 2) = "13" Then
fncOrdinal = "th"
Else
fncOrdinal = "rd"
End If
Case Else
fncOrdinal = "th"
End Select
End If

End Function

'*******************End Code**********************

5.) Run the query. I think you will see how the SQL only solution leaves
something to be desired, especially for the 11, 12 and 13th of the month.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom, I had it figured out in query based. It works perfectly well. Lots of
thanks.

Fel

 

[RPage]

Fel,
I'm new to Access, but I had the same need as you. After a lot of tinkering,
I think I got all of the dates to work with the following. You'll have to add
the space and the month after this, but this gets the days displaying
correctly.

=IIf(Format([Date],"dd")=11,"11th",(IIf(Format([Date],"dd")=12,"12th",(IIf(Format([Date],"dd")=13,"13th",(Day([Date])
& Nz(Choose(1+Day([Date]) Mod 10,"th","st","nd","rd"),"th")))))))

I hope it works for you. I also hope I posted the reply correctly since this
is my first time.

Richard Page

Tom, really appreciate your asisstance. But I still can't get the desired
results. Perhaps I will have to rephrase the sentence structure which don't
require me to have a suffix behind the dates.

Thanks anyway, Fel

***Repost--I don't see a copy of a reply I sent earlier***

Hi Fel,

I'm not sure what I'm doing wrong, but I can't get the query-only based
method to work, specifically for the 11th, 12th and 13th of the month. I made
some modifications to the function that I referenced earlier, which are shown
below. Try the following as an experiment:

1.) Create a table named Orders, with a field named ShippedDate.

2.) Add one record for each day of the month, for a month that includes 31
days, such as July. Just for fun, add one more record that does not include a
date in the ShippedDate field (null).

3.) Copy the following SQL statement and paste it into a new query:

SELECT Orders.ShippedDate,
Day([ShippedDate]) & fncOrdinal([ShippedDate])
AS DayOfMonthFunction,
Day([ShippedDate]) & Nz(Choose(1+Day([ShippedDate]) Mod
10,"th","st","nd","rd"),"th")
AS DayOfMonthSQL,
IIf(Day([ShippedDate]) & fncOrdinal([ShippedDate])=Day([ShippedDate]) &
Nz(Choose(1+Day([ShippedDate]) Mod 10,"th","st","nd","rd"),"th"),"Yes","NO")
AS [Are They Equal?]
FROM Orders
ORDER BY Orders.ShippedDate;

4.) Copy the following function into a new module. Name the module something
like basDateOrdinal (just don't name it the same as the function):

'*******************Begin Code*********************
Option Compare Database
Option Explicit

Function fncOrdinal(ByVal varDate As Variant) As String

Dim intNumber As Integer

If Not IsNull(varDate) Then
intNumber = Day(varDate)

Select Case Right$(intNumber, 1)
Case "1"
If Right$(intNumber, 2) = "11" Then
fncOrdinal = "th"
Else
fncOrdinal = "st"
End If
Case "2"
If Right$(intNumber, 2) = "12" Then
fncOrdinal = "th"
Else
fncOrdinal = "nd"
End If
Case "3"
If Right$(intNumber, 2) = "13" Then
fncOrdinal = "th"
Else
fncOrdinal = "rd"
End If
Case Else
fncOrdinal = "th"
End Select
End If

End Function

'*******************End Code**********************

5.) Run the query. I think you will see how the SQL only solution leaves
something to be desired, especially for the 11, 12 and 13th of the month.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom, I had it figured out in query based. It works perfectly well. Lots of
thanks.

Fel

 

[ms saintclair]

RPage,
Thanks, that was extrememly helpful, thank you!

Fel,
I'm new to Access, but I had the same need as you. After a lot of tinkering,
I think I got all of the dates to work with the following. You'll have to add
the space and the month after this, but this gets the days displaying
correctly.

=IIf(Format([Date],"dd")=11,"11th",(IIf(Format([Date],"dd")=12,"12th",(IIf(Format([Date],"dd")=13,"13th",(Day([Date])
& Nz(Choose(1+Day([Date]) Mod 10,"th","st","nd","rd"),"th")))))))

I hope it works for you. I also hope I posted the reply correctly since this
is my first time.

Richard Page

Tom, really appreciate your asisstance. But I still can't get the desired
results. Perhaps I will have to rephrase the sentence structure which don't
require me to have a suffix behind the dates.

Thanks anyway, Fel

***Repost--I don't see a copy of a reply I sent earlier***

Hi Fel,

I'm not sure what I'm doing wrong, but I can't get the query-only based
method to work, specifically for the 11th, 12th and 13th of the month. I made
some modifications to the function that I referenced earlier, which are shown
below. Try the following as an experiment:

1.) Create a table named Orders, with a field named ShippedDate.

2.) Add one record for each day of the month, for a month that includes 31
days, such as July. Just for fun, add one more record that does not include a
date in the ShippedDate field (null).

3.) Copy the following SQL statement and paste it into a new query:

SELECT Orders.ShippedDate,
Day([ShippedDate]) & fncOrdinal([ShippedDate])
AS DayOfMonthFunction,
Day([ShippedDate]) & Nz(Choose(1+Day([ShippedDate]) Mod
10,"th","st","nd","rd"),"th")
AS DayOfMonthSQL,
IIf(Day([ShippedDate]) & fncOrdinal([ShippedDate])=Day([ShippedDate]) &
Nz(Choose(1+Day([ShippedDate]) Mod 10,"th","st","nd","rd"),"th"),"Yes","NO")
AS [Are They Equal?]
FROM Orders
ORDER BY Orders.ShippedDate;

4.) Copy the following function into a new module. Name the module something
like basDateOrdinal (just don't name it the same as the function):

'*******************Begin Code*********************
Option Compare Database
Option Explicit

Function fncOrdinal(ByVal varDate As Variant) As String

Dim intNumber As Integer

If Not IsNull(varDate) Then
intNumber = Day(varDate)

Select Case Right$(intNumber, 1)
Case "1"
If Right$(intNumber, 2) = "11" Then
fncOrdinal = "th"
Else
fncOrdinal = "st"
End If
Case "2"
If Right$(intNumber, 2) = "12" Then
fncOrdinal = "th"
Else
fncOrdinal = "nd"
End If
Case "3"
If Right$(intNumber, 2) = "13" Then
fncOrdinal = "th"
Else
fncOrdinal = "rd"
End If
Case Else
fncOrdinal = "th"
End Select
End If

End Function

'*******************End Code**********************

5.) Run the query. I think you will see how the SQL only solution leaves
something to be desired, especially for the 11, 12 and 13th of the month.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Tom, I had it figured out in query based. It works perfectly well. Lots of
thanks.

Fel