How do I Sort by Calendar Date and Ignore the Year?

We have the arrival date (and in some cases the birth date) of several hundred equines (horses, donkeys, and mules) we have rescued in the last ten years.  Each calendar day we want to remember/celebrate an equine that arrived (or its birthday, if known).   So how do I sort my arrival date report by calendar day (month and day) and ignore the year it arrived (or was born)?  I use MS Access 2007.
bobbatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
SELECT AnimalName, ArrivalDate
FROM yourTable
WHERE Format([ArrivalDate], "mm") = Format(Date(), "mm")
ORDER BY Format([ArrivalDate], "mm-dd")

This will give you the name and arrival date of each animal which arrived during the current month, ordered by the date as month-day.  You need the two character "mm" and "dd" values or the values for 10 will immediately follow those for 1.
0
bobbatAuthor Commented:
Thank you.  We are close but the dates are not in order (see attached report).  Can you fix this?

My SQL:
SELECT tblHorses.strHorseBarnName, tblHorses.datDateFirstAcquired
FROM tblHorses
WHERE (((Format([tblHorses].[datDateFirstAcquired],"mm"))=Format(Date(),"mm")))
ORDER BY tblHorses.datDateFirstAcquired, Format([tblHorses].[datDateFirstAcquired],"mm-dd");
-0Alphabetical-List-Entire-History-Sorte
0
Dale FyeCommented:
Should be:

Order by Format(...

Leave out the first part

From my iPhone
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bobbatAuthor Commented:
Thanks, that worked:
SELECT tblHorses.strHorseBarnName, tblHorses.datDateFirstAcquired
FROM tblHorses
ORDER BY Format([tblHorses].[datDateFirstAcquired],"mm-dd");

But would if just want the horses for the current month only?
0
Dale FyeCommented:
That's why I had the WHERE clause in the first query:

SELECT tblHorses.strHorseBarnName, tblHorses.datDateFirstAcquired
FROM tblHorses
WHERE Format([tblHorses].[datDateFirstAcquired],"mm")=Format(Date(),"mm")
ORDER BY Format([tblHorses].[datDateFirstAcquired],"mm-dd");

That WHERE clause could also read:

WHERE Month([tblHorses].[datDateFirstAcquired])=Month(Date())

but I figured since you were already using the Format function.

SELECT tblHorses.strHorseBarnName, tblHorses.datDateFirstAcquired
FROM tblHorses
WHERE Month([tblHorses].[datDateFirstAcquired])=Montht(Date())
ORDER BY Day([tblHorses].[datDateFirstAcquired]);

With the where clause, you don't need to worry about months coming up out of sequence, so you could also do it like:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bobbatAuthor Commented:
Thanks
This worked for the current month only:
 SELECT tblHorses.strHorseBarnName, tblHorses.datDateFirstAcquired
 FROM tblHorses
 WHERE Month([tblHorses].[datDateFirstAcquired])=Montht(Date())
 ORDER BY Day([tblHorses].[datDateFirstAcquired]);
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.