sort by date

[Guest]

I want to make a birthday list from my employee table that only shows the day
and month, not the year. The dates were originally inputted to the table in
dd/mm/yyyy format.

Thanks!

 

[Pat Hartman \(MVP\)]

Dates can be formatted for display however you like. Use the format
property of the control on the form or report to show just the month and day
if that's what you want. For example to show Mar 21, place the following in
the format property of the birthday field:

mmm dd

 

[Guest]

To sort you would want the format like this --
"mmdd - d mmmm"

 

[Pat Hartman \(MVP\)]

To sort, you wouldn't use the formatted field at all you would sort on the
unformatted table field. Notice I suggested formatting at the form/report
level rather than in a query. It is when you format in your query that you
run into the sorting issue if you have formatted your date thereby turning
it into a text string. Text strings sort character by character, left to
right (which is why October comes before February) whereas unformatted date
fields are sorted in numerical order.

 

[Guest]

I usually use two output fields - one for sort and the other for display. In
my formating I used 2 digit month and two digit day to make it sort correctly.

 

[John W. Vinson]

I want to make a birthday list from my employee table that only shows the day
and month, not the year. The dates were originally inputted to the table in
dd/mm/yyyy format.

I'd suggest using a calculated field:

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

This will calculate this year's birthday anniversary; it can be sorted
chonologically to (e.g.) show a February 1952 birthday before a March 1985
birthday; and it can be formatted any way you like.

John W. Vinson [MVP]