Grouping by Month/Year in Report

[Montez659]

I am having trouble getting my report to populate like I would want, and I am sure that this is a fairly common problem with a common solution, but I am just not getting the result that I want.

Basically, I have many transactions that will take place in the future and I want to display them on a report. My table has a field futTransDateProcessed which is where I get the date from. From there, I built two expressions in a query, one to pull the month and the other to pull the year (showMonth, showYear) and added these as grouping levels.

The problem seems to be that it want's to form a new group anytime the date is different, instead of anytime the month or year is different. For instance, since 7/26/2011 and 7/27/2011 are two different dates, there will be two different headers, each saying July 2011. I would want all of the July dates grouped under one heading.

What am I doing wrong? Here is the SQL from the query that this report is based off of:

SELECT tblFutureTransactions.futTransAmount, tblFutureTransactions.futTransDateProcessed, MonthName(Month([futTransDateProcessed])) AS displayMonth, tblFutureTransactions.futTransDescription, Month([futTransDateProcessed]) AS monthNum, Year([futTransDateProcessed]) AS yearNum
FROM tblFutureTransactions
ORDER BY tblFutureTransactions.futTransDateProcessed;

 

[Norie]

Why is there no group by clause in the query?

 

[Montez659]

I didn't think about that. I was trying to do all the grouping within the report itself.

 

[Montez659]

And...that did it. Thanks Norie!

 

[Norie]

Montez

I think it is possible to do it in the report.

Why not try using the the report wizard?

I think that might have options you can use.

If you get it working that way you can look at how the report's been formatted etc.

 

[Montez659]

I think I originally used the wizard, but that may have been before I created the expressions for Month and Year. I know sometimes the reports can get finicky if you try to group and sort after it has been created. I think I will go back and try the wizard again now that I have all the fields set the way they need to be.

I will post back to let the world know whether it works or not.