Inserting a Sum into a List Group

[AnnieRider]

The grouping in my report is done in List form and looks as such

Interventions Total: 2

Name: Joe Bloggs Date Start: 18/01/2009 Date Entered: 27/01/2009 Entered After (working days): 7
Organisation: Purbeck School
Notes: Saw Joe in school, discussed Options after Year 11.

Name: Jane Smith Date Start: 24/08/2008 Date Entered: 24/08/2008 Entered After (working days): 0
Organisation: Job Centre Plus
Notes: Helped Jane create her CV and looked at Retail vacancies

Telephone/Email Contact Total: 2

Name: Jane Smith Date Start: 22/08/2008 Date Entered: 24/08/2008 Entered After (working days): 2
Organisation:
Notes: Called Jane to remind her of our appt at Job Centre.

Name: Daniel Doors Date Start: 02/12/2008 Date Entered: 10/12/2008 Entered After (working days): 6
Organisation:
Notes: Have emailed Joe's Tutor to let him know about the appointment I've arranged for him on Weds 12th November

----------------------------------------------------------
I have another text box on each record that denotes whether the datediff is 5 or above

=IIf(ReportItems!datediff.Value>=5,1,0)

For each group (ie Interventions and Tel Contact) I need to show the sum of the above, so how many records in each group have a datediff of over 5.

I have added a text box with the following expression within my group list

=Sum(ReportItems!LateEntry.Value)

but when I run the report I'm told that I can only put Aggregate functions in headers or footers. The sum works if I put it in the header but of course it counts the total for the report and I need individual totals for each group.

Is there any work around for showing a sum for a group that is created in list form?

Thanks
Annie

 

[xlbo]

and this:

=SUM(IIf(ReportItems!datediff.Value>=5,1,0))

Or =SUM(IIf(Expression that createsReportItems!DateDiff>=5,1,0))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnnieRider]

Hi Geoff

The first suggestion still tells me I can't put an aggregate outside a header or footer.

The second, I'm not sure which expreesion to put in.

I've got-
=DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbMonday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbTuesday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbWednesday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbThursday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbFriday)[/b}
--in a text box named datediff which counts the days between event & input for each record

and I've got-
=IIf(ReportItems!datediff.Value>=5,1,0)
--in textbox called LateEntry.

I want to count the records where LateEntry is true, =1

I've tried replacing your comment with both, neither seem to work but I'm not sure I putting the right info in the right place.

Sorry, really confused

 

[xlbo]

try this:

=SUM(IIf(DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbMonday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbTuesday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbWednesday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbThursday) +
DateDiff ("ww", Fields!DateStart.Value, Fields!DateEntered.Value, vbFriday)>=5,1,0))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnnieRider]

I thought that was what I had put in after your first reply (I tried both) but I'm thinking now I may have pasted over one character maybe and not noticed because it works now - now I feel really stupid!

Thanks ever so (again!)

 

[xlbo]

no worries - most likely offenders are brackets!

That way of wrapping an IIF in a SUM is the best way of doing aggregates in text boxes IMO - pretty powerful too as long as you can use a true/false test to return a 1 or 0

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question