Date Format

***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" wrote:

> Tom, I had it figured out in query based. It works perfectly well. Lots of
> thanks.
>
> 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

"Tom Wickerath" wrote:

> ***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" wrote:
>
> > Tom, I had it figured out in query based. It works perfectly well. Lots of
> > thanks.
> >
> > Fel