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

[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

 

[Duane Hookom]

Try sort by Format([Birthday],"mmdd")
or sort by Month([Birthday]) & Day([Birthday])

 

[Joseph Ellis]

Try sort by Format([Birthday],"mmdd")
or sort by Month([Birthday]) & Day([Birthday])

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

 

[Duane Hookom]

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

Try sort by Format([Birthday],"mmdd")
or sort by Month([Birthday]) & Day([Birthday])

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

 

[Joseph Ellis]

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")

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