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

I have a table that has a list of employees date of birth. That field is
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6823-sorting-birthdate-month-day-instead-month.html
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/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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
>
> 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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
> John
>

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
for the query.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823

Any ideas?

- John

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
>year.

An alternative approach is to include a calculated field:

HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

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]

Edit the "mmdd" to "mm"&"-"&"dd" in your Format.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6823
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
> 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
> for the query.
>
> Any ideas?
>
> - John
>
>

Try mm\-dd

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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
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
> for the query.
>
> Any ideas?
>
> - John
>

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.

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