sorting short dates, without using years

[kgatchell2001]

Hi, I am a beginner working on a database to manage clients, payroll,
station assignments, etc. I have created a query which calculates age
based on subtracting the date of birth from the current date. This
works fine and date of birth is entered as month, day, year in
standard short date format (99/99/0000;;).

Here is the problem, I would also like to print out a list of all
clients who have a birthday in any given month. Presently, when I
attempt to sort the list, it takes the whole date into account and
uses the year as well. Therefore, a person born in November of 1945
will be listed before a person born in January 1975. Is there a way to
sort by month and date only without entering a new variable for date
of birth? Can you ask Access to sort a short date without using the
year? Thank you in advance.

Kgatchell2001

[fredg]

On 24 Jun 2004 13:56:24 -0700, kgatchell2001 wrote:

Hi, I am a beginner working on a database to manage clients, payroll,
station assignments, etc. I have created a query which calculates age
based on subtracting the date of birth from the current date. This
works fine and date of birth is entered as month, day, year in
standard short date format (99/99/0000;;).

Here is the problem, I would also like to print out a list of all
clients who have a birthday in any given month. Presently, when I
attempt to sort the list, it takes the whole date into account and
uses the year as well. Therefore, a person born in November of 1945
will be listed before a person born in January 1975. Is there a way to
sort by month and date only without entering a new variable for date
of birth? Can you ask Access to sort a short date without using the
year? Thank you in advance.

Kgatchell2001

In the query, add another column:
SortColumn:Format([DateOfBirth],"mm dd")
You can uncheck the Show check box as you don't need to display this
column.
Sort by this column.

Note... You haven't stated what expression you are using to calculate
age, but subtracting the date of birth from the current date may not
be accurate when computing the difference in years, i.e. someone born
on 12/31/2003 will be shown to be 1 year old on 1/1/2004.

Here is an expression you can use which is accurate.
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Hope this helps.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.