Report does not display same as query 1

[kleinicus]

I have a query that calculates totals and groups them by month, in chronological order. Here is the code...

SELECT Format$([Date],'mmmm yyyy') AS [Date By Month], Count(*) AS [Total Policies], Sum(TransactionsTable.Amount) AS [Total $]
FROM TransactionsTable
WHERE (((TransactionsTable.Agent)='S'))
GROUP BY Format$([Date],'mmmm yyyy'), Format$([Date],'yyyymm')
ORDER BY Format$([Date],'yyyymm');

This query was constructed this way because Access previously displayed the months in alphabetical order, rather than chronological.
My problem is that when I try to create a report based on that query, the months are displayed alphabetically instead of chronologically.
Can anyone help me get the months to display correctly?
Thanks.

SK

 

[straybullet]

Im not 100% sure this would work, but if you were to use an unbound textbox on your report (having something along the lines of DatePart(m,[Date]) as its source), you should be able to sort the report on it.
You may have to set the source in the OnFormat event of the section for it to work, rather than setting the source in the actual textbox.

Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

Another option (figures I thought of it after hitting submit!)
add DatePart(m,[Date]) As DateSort to your query and sort the report by it - using the Sorting and Grouping button on the toolbar.

Let them hate - so long as they fear... Lucius Accius

 

[kleinicus]

I tried adding the SQL code you mentioned so that now my code looks like this...

SELECT Format$([Date],'mmmm yyyy') AS [Date By Month], Count(*) AS [Total Policies], Sum(TransactionsTable.Amount) AS [Total $], DatePart(m,[Date]) AS DateSort
FROM TransactionsTable
WHERE (((TransactionsTable.Agent)='Sara'))
GROUP BY Format$([Date],'mmmm yyyy'), Format$([Date],'yyyymm')
ORDER BY Format$([Date],'yyyymm');

When I try to run the report like this, Access says that DatePart(m,[Date]) is not part of an aggregate function.

Any suggestions?

 

[VRoscioli]

Reports in Access typically ignore the sorting found in the query itself. You need to set your sorting critera (Format$([Date],'yyyymm')) in the Order By property of the Report.


-V

 

[dhookom]

Change your query to:

SELECT Format([Date],'yyyymm') AS [SortYM], Format([Date],'mmmm yyyy') AS [Date By Month], Count(*) AS [Total Policies], Sum(TransactionsTable.Amount) AS [Total $]
FROM TransactionsTable
WHERE (((TransactionsTable.Agent)='S'))
GROUP BY  Format([Date],'yyyymm'), Format([Date],'mmmm yyyy');

Then use the Sorting and Grouping dialog in the report design to set the sorting to [SortYM].

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kleinicus]

Where exactly do I find the Order By property of the report?

 

[kleinicus]

Duane, thanks for the query change. I changed the code and it displays properly in both the query results and in the report. Thanks a million!

 

[kleinicus]

Duane, the change that you suggested worked just fine except for one very tiny thing. At the end of the report, an error message pops up and says "No Current Record" and the last page of the report shows two fields called #Error.

Any suggestions on that?

SK

 

[dhookom]

What section contains the problem controls and what are the Control Sources?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]