Qurey By Last 3 Months

[tingler0]

I have a query that is generating a daily average received in $ per month, but when i use the ">=DateAdd("m",-3,Date())" for the criteria it is pulling the last 90 days.

Here is the SQL for the query;

SELECT Format([Bank_Statement_Summary].[GL_Date],"mmmm-yyyy") AS [Month], Sum(Bank_Statement_Summary.Revenue_Daily_Total) AS [Average $ Received] INTO tbl3MonthAvg
FROM Bank_Statement_Summary
GROUP BY Format([Bank_Statement_Summary].[GL_Date],"mmmm-yyyy"), Year([GL_Date]), Bank_Statement_Summary.GL_Date
HAVING (((Year([GL_Date]))=Year(Now())) AND ((Bank_Statement_Summary.GL_Date)>=DateAdd("m",-3,Date())));

I need this to only genterat the last three months by month name and not by days.

Can anyone help Please! Thanks inadvance.

 

[MarkK]

Don't GROUP BY Bank_Statement_Summary.GL_Date. If you add the DateAdd() in the query design grid, select WHERE rather than GROUP BY

 

[tingler0]

Thanks but I'm still getting August, Sept, Oct and Nov. How would i get the current month plus the last two?

 

[MarkK]

Format(DateAdd("m", -2, Date()), "1-mmm-yy")

 

[tingler0]

Thanks for you help again, I am a newbie at this. This is what it is genreating
qryLast3MonthlyAvgMonthAverage $ ReceivedAugust 2009$26,578,370.37November 2009$57,661,802.05October 2009$70,473,856.75September 2009$67,832,311.22

and the SQL behind the query i am using, I changed the group by to Where but it appears to still being returning last 90 days rather than Sept, Oct and Nov.

SELECT Format([Bank_Statement_Summary].[GL_Date],"mmmm yyyy") AS [Month], Sum(Bank_Statement_Summary.Revenue_Daily_Total) AS [Average $ Received] INTO tbl3MonthAvg
FROM Bank_Statement_Summary
WHERE (((Bank_Statement_Summary.GL_Date)>=DateAdd("m",-3,Date())))
GROUP BY Format([Bank_Statement_Summary].[GL_Date],"mmmm yyyy"), Year([GL_Date])
HAVING (((Year([GL_Date]))=Year(Now())));

 

[boblarson]

Thanks for you help again, I am a newbie at this. This is what it is genreating
qryLast3MonthlyAvgMonthAverage $ ReceivedAugust 2009$26,578,370.37November 2009$57,661,802.05October 2009$70,473,856.75September 2009$67,832,311.22

and the SQL behind the query i am using, I changed the group by to Where but it appears to still being returning last 90 days rather than Sept, Oct and Nov.

SELECT Format([Bank_Statement_Summary].[GL_Date],"mmmm yyyy") AS [Month], Sum(Bank_Statement_Summary.Revenue_Daily_Total) AS [Average $ Received] INTO tbl3MonthAvg
FROM Bank_Statement_Summary
WHERE (((Bank_Statement_Summary.GL_Date)>=DateAdd("m",-3,Date())))
GROUP BY Format([Bank_Statement_Summary].[GL_Date],"mmmm yyyy"), Year([GL_Date])
HAVING (((Year([GL_Date]))=Year(Now())));

Create a field in your query like this:

QDateatePart("m",Format([Bank_Statement_Summary].[GL_Date])

And then in your criteria use

DatePart("m",Date) OR DatePart("m", Date)+1 Or DatePart("m", Date)+2

 

[raskew]

Hi -

As an alternative, try playing with this:

Today is: 11/24/2009
? dateadd("m", -2, date() - day(date())+1)
9/1/2009

place this as criteria for your datefield:
>= dateadd("m", -2, date() - day(date())+1)

Best wishes - Bob