How do I sort by day and group by month?

[sandingdude]

How do I sort by day and group by month?

I know the sorting features of Access. I'm running a report with dates from 11/1/02 to 9/26/03. I need to group all the data by month and give a total and I need to sort within that group by date. When I turn on grouping by month all the dates get unsorted. Any ideas?

Thanks,

Dave

 

[KenReay]

Hi

In your query, make a couple of 'calculated fields' day and month using Day(YourDate) and Month(YourDate), something like:

MyDayay(MyDate)
MyMonth:Month(myDate)

In your report sort and group on the calculated fields MyDay and MyMonth

I am assuming that you are aware that the order of a report is dependant on the sorting and grouping specified in the report, not the ORDER BY clause of the underlying query

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sandingdude]

Thanks Ken

So basically I just have to break down the date field in a query and sort by each field?

Dave

 

[hneal98]

In the report, you can setup the groups by month based on the date field. there is no need to go back to your query.

 

[dhookom]

Make sure your sorting and grouping sorts first by month and then by date. I would set up the expressions like:
=Format([YourDateField],"yyyymm&quot
[YourDateField]

Duane
MS Access MVP