Report Sort Problem 1

[imagranny]

imagranny (TechnicalUser) Oct 17, 2002
I can not get my report to group in date order. I have tried several options in the "sorting and grouping" table but nothing seems to work. I can get the dates to appear in the correct order but can not get the "monthly" break in the correct order. When the dates are out of sequence, I can get a break every 4 weeks (for the monthly sum). I have pulled this report from a query. The query looks to be sorted correctly when I review it. Each record has a weekly date attached to it and I want to pull the report for the entire year, in date order, with a subtotal for each month. Any suggestions?

 

[kennethK]

Try the Wizard..

I don't know if that would help, but I thought I done something like that before with the Report Wizard.

 

[imagranny]

Thanks, but I started with the wizard and it hasn't helped with my sort problem.

 

[PaulBricker]

Can you post a sample of the data. It would help to see what the date fields look like. Also, could you explain what dates out of sequence and monthly break refer to. It sounds like you want totals every 4 weeks regardless of whether the date is in one month or another. Just some more detail would help.

Paul

 

[imagranny]

Here is some sample data on my report:

Jane Doe Ave
1//7/02 28.8 690 24
1/28/02 31.5 867 28
1/21/02 24.9 588 24
1/14/02 7 130 19

Monthly Sum 92.2 2,275 25


I have tried the report wizard, but that has not helped. I have checked that my query puts dates in ascending order and the groupings in the report design also show the dates in ascending order. I have no trouble getting the date to group by month if there are five weeks. For example April looks like this:

4/22/02
4/29/02
4/15/02
4/1/02
4/8/02
Monthly Sum

I appreciate any help I can get. I need the dates to be in order.

Thanks!

 

[PaulBricker]

Sorry g but I'm still confused. You indicate you want the dates in ascending order but neither of the date lists in your example are ascending. They seem somewhat random except they are all the same month. My main question is do your want your grouping every 4 weeks or every month?

Paul

 

[imagranny]

The example I used shows how the report looks, not how I want it to look. By chosing the "month" option in the sorting and grouping, I am getting all the weeks in the month to group together. What I am not getting is the weeks to print in the right date order.

I Want This: Report is doing this:

4/7/02 4/14/02
4/21/02 4/7/02
4/28/02 4/28/02


Can you help?

 

[PaulBricker]

Try this. In your underlying query put this in a new column
MySort:[DateField]*24
this will return a long integer that represents your date. Sort this field ascending and get rid of the other sorting in the query. Then in the report Sorting and Grouping add your Date field and sort ascending as set the interval to Month and under your date field add MySort and sort ascending. This should return the values in your report in ascending order.

Paul