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

[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

 

[Douglas J. Steele]

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

 

[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

 

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

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]

 

[Guest]

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.

 

[Douglas J Steele]

Try mm\-dd

 

[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