How to get past dates from previous years?

[Neal Moriarty]

Here's my SQL so far...

SELECT Format(Day([Actual Date]=Day(Date())) And Month([Actual Date]=Month(Date()))) AS "Past Date"
FROM [Gas Data]
ORDER BY Format([Actual Date],"mm/dd/yyyy") DESC , [Gas Data].[MCF sendout] DESC , [Gas Data].[Degree Days] DESC;

What I'm trying to do is get the same past date from the last three years for a report. For instance, for today I would need 12/3/2009, 12/3/2008 and 12/3/2007. There's a field called "Actual Date" which I'm hoping I can parse down to return the day and month for my report, but so far no success...any help would be greatly appreciated!!!

Many thanks!
Neal

[Stewart Ross]

Hi. There are some problems with your SQL, in that you've mixed up what you are trying to select with how you want to select it.

You want to select all rows in which the [Actual Date] field matches the current day and month. Try:

SELECT   [Actual Date] AS [Past Date]
FROM     [Gas Data]
WHERE    Month([Actual Date]) = Month(Date()) 
AND      Day  ([Actual Date]) = Day(Date())
ORDER BY [Actual Date] DESC;

Be aware if you use FORMAT that it returns text values, and you cannot order dates formatted as text unless you return them with the year reversed: yyyy/mm/dd for example. There is, however, no need to use format for what you want to do.

-Stewart

PS if there are no rows in table [Gas Data] with [Actual Dates] matching the current day and month then the query will not return any rows at all. Depending on what you do with this query for your report you may not find it of practical use in itself.

[Neal Moriarty]

Awesome Stewart! That did it, works perfectly now. I knew what I wanted it to do, just could bend the SQL to my will to do so. Thanks a million!

Neal