By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

sorting short dates, without using years

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.