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

J

John

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
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
 
D

Douglas J. Steele

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

JoJa15

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
12/25/2005. I have tried this in the format property for the report and
for the query.

Any ideas?

- John
 
J

John Vinson

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.
Click to expand...

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
formated, sorted, searched etc. just like any other date/time field.

John W. Vinson[MVP]
 
D

Dlimey

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.
 
J

JoJa15

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
 
Top