Sorting Birthdate by month-day instead of by month-day-year

#1 (**permalink**) 07-Nov-2005, 11:26 AM

I have a table that has a list of employees date of birth. That field is
named "DOB". I want to create a report that prints out everyones month and
day of birth and sorts on that instead of sorting by their month, day, and
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6823-sorting-birthdate-month-day-instead-month.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
year.

For example say I have the following data:

Name: DOB:
employee_1 1/15/1900
employee_2 1/1/2000
employee_3 2/1/2000

I would like the data to be printed like the following:

employee_2 1/1
employee_1 1/15
employee_3 2/1

Currently sorting on DOB in the report yields the following though because
employee_1 was born before employee_2

employee_1 1/15
employee_2 1/1
employee_3 2/1

I have looked and looked and tried many different things without success.
Can anyone help me out? Thank you in advance to anyone that can help me out.

Best Regards,
John

#2 (**permalink**) 07-Nov-2005, 11:26 AM

Create a query, and add a computed field to the query: MonthDay:
Format([DOB], "mmdd"). Sort on that field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"John" wrote in message
news:usK73lz4FHA.4076@tk2msftngp13.phx.gbl...
>I have a table that has a list of employees date of birth. That field is
>named "DOB". I want to create a report that prints out everyones month and
>day of birth and sorts on that instead of sorting by their month, day, and
>year.
>
> For example say I have the following data:
>
> Name: DOB:
> employee_1 1/15/1900
> employee_2 1/1/2000
> employee_3 2/1/2000
>
> I would like the data to be printed like the following:
>
> employee_2 1/1
> employee_1 1/15
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
> employee_3 2/1
>
> Currently sorting on DOB in the report yields the following though because
> employee_1 was born before employee_2
>
> employee_1 1/15
> employee_2 1/1
> employee_3 2/1
>
> I have looked and looked and tried many different things without success.
> Can anyone help me out? Thank you in advance to anyone that can help me
> out.
>
> Best Regards,
> John
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
>

#3 (**permalink**) 07-Nov-2005, 11:26 AM

That worked great! I had actually tried the format command before, but
in my report and not in a query.

The problem I have now is how to display the data as mm-dd. In the
report in the format property I entered mm-dd, then I tried ##-##, then
00"-"00 and the data still displays as mmdd or 1225 for a birthday on
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
12/25/2005. I have tried this in the format property for the report and
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
for the query.

Any ideas?

- John

#4 (**permalink**) 07-Nov-2005, 11:26 AM

On Sun, 6 Nov 2005 19:20:42 -0600, "John"
wrote:

>I have a table that has a list of employees date of birth. That field is
>named "DOB". I want to create a report that prints out everyones month and
>day of birth and sorts on that instead of sorting by their month, day, and
>year.

An alternative approach is to include a calculated field:

HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823

This will contain the current year's birthday anniversary, and can be
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
formated, sorted, searched etc. just like any other date/time field.

John W. Vinson[MVP]

#5 (**permalink**) 08-Nov-2005, 12:52 PM

Edit the "mmdd" to "mm"&"-"&"dd" in your Format.
This should make it read with the 'dash' between month and day.
The sort will still be correct.

"JoJa15" wrote:

> That worked great! I had actually tried the format command before, but
> in my report and not in a query.
>
> The problem I have now is how to display the data as mm-dd. In the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
> report in the format property I entered mm-dd, then I tried ##-##, then
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
> 00"-"00 and the data still displays as mmdd or 1225 for a birthday on
> 12/25/2005. I have tried this in the format property for the report and
> for the query.
>
> Any ideas?
>
> - John
>
>

#6 (**permalink**) 08-Nov-2005, 12:52 PM

Try mm\-dd

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
(no e-mails, please!)

"JoJa15" wrote in message
news:1131329881.001483.4590@g14g2000cwa.googlegrou ps.com...
> That worked great! I had actually tried the format command before, but
> in my report and not in a query.
>
> The problem I have now is how to display the data as mm-dd. In the
> report in the format property I entered mm-dd, then I tried ##-##, then
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
> 00"-"00 and the data still displays as mmdd or 1225 for a birthday on
> 12/25/2005. I have tried this in the format property for the report and
> for the query.
>
> Any ideas?
>
> - John
>

#7 (**permalink**) 08-Nov-2005, 12:53 PM

I tried the different formating suggestions above but oddly enough this
is what worked in the fields format box on the report:

-&&

I did not get this by any knowledge but by trial and error after I
tried the two above formating suggestions.

- John

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823