Date of Birth

[Heather]

I have a list of dates of birth and I need to sort them by month and day only
and not year. I know that there is a way to do this but can't remember the
fx. Can someone provide me with it.
Thanks in advance,
Heather

 

[Wayne-I-M]

Hi Heather

Add 2 columns to a query

DateOfBirthDAYatePart("d",[TableName]![DateofBirth])
and
DateOfBirthMONTHatePart("m",[TableName]![DateofBirth])

and sort by these - something like this

SELECT DatePart("d",[TableName]![DateofBirth]) AS BirthDayDAY,
DatePart("M",[TableName]![DateofBirth]) AS BirthDayMONTH
FROM TableName
ORDER BY DatePart("d",[TableName]![DateofBirth]),
DatePart("M",[TableName]![DateofBirth]);

 

[Caitlyn Rhodes]

Sorry i can't help you there!
Caitlyn

 

[Duane Hookom]

Whoa... Since this is a report, you can't rely on the sorting in the report's
recordsource query. All sorting in a report should be performed in the
Sorting and Grouping dialog in the report design view. You can enter the
expressions:
=Month([DateOfBirth])
=Day([DateOfBirth])
Or just
=DateFormat([DateOfBirth],"MMDD")
I generally prefer the two expressions since it is easier to group on the
month.
--
Duane Hookom
Microsoft Access MVP

Wayne,

The US expression for dates is MMDDYY. Presumably Heather is US so the Order
By clause in your query needs to be:

ORDER BY
DatePart("M",[TableName]![DateofBirth]),DatePart("d",[TableName]![DateofBirth]);

Steve
(e-mail address removed)

Hi Heather

Add 2 columns to a query

DateOfBirthDAYatePart("d",[TableName]![DateofBirth])
and
DateOfBirthMONTHatePart("m",[TableName]![DateofBirth])

and sort by these - something like this

SELECT DatePart("d",[TableName]![DateofBirth]) AS BirthDayDAY,
DatePart("M",[TableName]![DateofBirth]) AS BirthDayMONTH
FROM TableName
ORDER BY DatePart("d",[TableName]![DateofBirth]),
DatePart("M",[TableName]![DateofBirth]);



--
Wayne
Trentino, Italia.

I have a list of dates of birth and I need to sort them by month and day
only
and not year. I know that there is a way to do this but can't remember
the
fx. Can someone provide me with it.
Thanks in advance,
Heather

 

[Duane Hookom]

Sorry about that. I thought this was a report question. If you want to
display the results in a report, ignore the sorting in the query.
--
Duane Hookom
Microsoft Access MVP

Whoa... Since this is a report, you can't rely on the sorting in the report's
recordsource query. All sorting in a report should be performed in the
Sorting and Grouping dialog in the report design view. You can enter the
expressions:
=Month([DateOfBirth])
=Day([DateOfBirth])
Or just
=DateFormat([DateOfBirth],"MMDD")
I generally prefer the two expressions since it is easier to group on the
month.
--
Duane Hookom
Microsoft Access MVP

Wayne,

The US expression for dates is MMDDYY. Presumably Heather is US so the Order
By clause in your query needs to be:

ORDER BY
DatePart("M",[TableName]![DateofBirth]),DatePart("d",[TableName]![DateofBirth]);

Steve
(e-mail address removed)

Hi Heather

Add 2 columns to a query

DateOfBirthDAYatePart("d",[TableName]![DateofBirth])
and
DateOfBirthMONTHatePart("m",[TableName]![DateofBirth])

and sort by these - something like this

SELECT DatePart("d",[TableName]![DateofBirth]) AS BirthDayDAY,
DatePart("M",[TableName]![DateofBirth]) AS BirthDayMONTH
FROM TableName
ORDER BY DatePart("d",[TableName]![DateofBirth]),
DatePart("M",[TableName]![DateofBirth]);



--
Wayne
Trentino, Italia.



:

I have a list of dates of birth and I need to sort them by month and day
only
and not year. I know that there is a way to do this but can't remember
the
fx. Can someone provide me with it.
Thanks in advance,
Heather