Sort by the day or month in a Month/Day/Year field

  • Thread starter Thread starter Joseph Ellis
  • Start date Start date
J

Joseph Ellis

Hello all,

I have a small table of church members' information, including their
birthdays. I've made a query that assembles a list of records with
non-blank birthdays, and I'd like to sort that list by birthday MONTH
and DAY, with the year being the least important. The ultimate goal
is to be able to pull up a list of, say, all the birthdays in
December, ordered by day.

So far I've just got

ORDER BY Individuals.Birthday

But of course that takes the year into account, which I don't want.

I know nothing about SQL, but am willing to learn the basics at least,
if need be. Any suggestions for a good website for beginners would be
most appreciated as well.

Thanks,
Joseph
 
Try sort by Format([Birthday],"mmdd")
or sort by Month([Birthday]) & Day([Birthday])
 
Try sort by Format([Birthday],"mmdd")
or sort by Month([Birthday]) & Day([Birthday])
Click to expand...

Thanks so much, that's exactly what I needed to get me going in the
right direction.

Just for the record, now I'm using

ORDER by Month([Individuals.Birthday]) & Day([Individuals.Birthday])

Thanks again,
Joseph
 
Keep in mind that if you concatenate them like you did, 1023 will come
before 21. You would be better off either sorting on two separate columns of
Month and Day or Format([Birthday], "mmdd")

--
Duane Hookom
MS Access MVP


Joseph Ellis said:
Try sort by Format([Birthday],"mmdd")
or sort by Month([Birthday]) & Day([Birthday])
Click to expand...

Thanks so much, that's exactly what I needed to get me going in the
right direction.

Just for the record, now I'm using

ORDER by Month([Individuals.Birthday]) & Day([Individuals.Birthday])

Thanks again,
Joseph
Click to expand...
 
Keep in mind that if you concatenate them like you did, 1023 will come
before 21. You would be better off either sorting on two separate columns of
Month and Day or Format([Birthday], "mmdd")
Click to expand...

Yes, I found that out right away <g>. I ended up just making the
query into a parameter query, with the month being the parameter.
Then I sort on the day using:

ORDER BY Day([Individuals.Birthday])

I haven't messed around with Format() yet, but I did end up just
changing the format of the Birthday field to something like "mmm dd"
within the Individuals table itself, achieving the same basic effect,
I think.

Thanks again for your help.

Joseph
 
Back
Top