Monthly history query 1

[baugie]

I am trying to create a query to group on the data by month for the previous year. I have the query grouping by month, but can't get it to stop at the data prior to one year earlier. Here is an example of my query:

SELECT (Format([shDate],"mmm yyyy&quot) AS [Month], (equation as [Results])
FROM [TableName]
GROUP BY (Format([shDate],"mmm yyyy&quot)
HAVING ((((Format([shDate],"mmm yyyy&quot))>format(DateAdd("y",-1,Now()),"mmm yyy&quot))

This retrieves every month in the database. Suggestions?

Thanks

baugie

 

[scriverb]

Haven't tested this but you only have 3 y's in the final Format statement. Try this:

SELECT (Format([shDate],"mmm yyyy&quot) AS [Month], (equation as [Results])
FROM [TableName]
GROUP BY (Format([shDate],"mmm yyyy&quot)
HAVING ((((Format([shDate],"mmm yyyy&quot))>format(DateAdd("y",-1,Now()),"mmm yyyy&quot))


Bob Scriver

 

[scriverb]

Sorry, you have to turn around the year and month for the comparison:
SELECT (Format([shDate],"mmm yyyy&quot) AS [Month], (equation as [Results])
FROM [TableName]
GROUP BY (Format([shDate],"mmm yyyy&quot)
HAVING ((((Format([shDate],"yyyy mmm&quot))>format(DateAdd("y",-1,Now()),"yyyy mmm&quot))

Let me know how this works.
Bob Scriver

 

[baugie]

OOPS...I had caught that earlier also. This only takes out all months equal to the current month. However, all other months are still included.

The key is stopping it from going back beyond one year. For some reason it is not using the DateAdd function to correctly identify the date.

Thanks though

Brian

 

[baugie]

I was a bit late with my reply. Using the reversed "yyyy mmm" formating, it only selects months from the current year because it is comparing all years greater than last year. Perhaps I need to use a conditional based upon the year of the date in the table. I'll give it a go here.

Thanks,

Brian

 

[scriverb]

Sorry, about the previous posts. I think this should work for you. We have to use the actual date rather than the formated representation of the date in your Having statement.
SELECT (Format([shDate],"mmm yyyy&quot) AS [Month], (equation as [Results])
FROM [TableName]
GROUP BY (Format([shDate],"mmm yyyy&quot)
HAVING [shDate] >= DateSerial(DatePart("yyyy", date) - 1, DatePart("m", date), 1) Bob Scriver

 

[baugie]

Looks like you got it for me Bob!

Thanks!!!

Brian

 

[scriverb]

Well if you try and try and try. Every once in a while you get it right in the end. That is what testing is for. Bob Scriver