asked on

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.

Dale Fye

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.

Robert Batchelor

ASKER

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

Dale Fye

Should be:

Order by Format(...

Leave out the first part

From my iPhone

Robert Batchelor

ASKER

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?

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Batchelor

ASKER

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]);