Sum of calculated fields

[Rickalty]

Hello again..... still can't get this report right

I have a textbox in the body of my report called "QualShip". The control source is :-

=IIf([Qual]="Y",([ShipFig]),0)

This works fine. If the trip is within tolerance the value of [ShipFig] appears in [QualShipFig], otherwise it shows zero. So far so good. But... in the report footer I have a textbox called SumShip, whose control source is :-

=Sum([ShipFig]}

This field also works fine, shoing the total of all ship figure fields. Another field though is called SumQualShip. The control source is :-

=Sum([QualShip]}

This doesn't work :-( When I preview the report, a dialog box asks for a parameter value for "ShipFig". If I leave it blank the area where SumQualShip should appear is blank, if I put a value in I get an SQL error.

What I want , obviously, is the total of "ShipFig" for all records where "Qual" is Y, which should also be the total of "QualShip".

All relevant fields, by the way, are formatted as General Numbers with zero decimal places.

What am I doing wrong, please ?

 

[danvlas]

=Sum(IIf([Qual]="Y",([ShipFig]),0))

should do.

To sum a calculated control, you need to sum its entire formula.

Good luck


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[Rickalty]

Thanks very much.... I reread my guide book after reading your post, and couldn't find any reference to not being able to sum a calculated control directly.

This is a simple report, but having to sum the whole formula would get really unwieldy in a more complicated one.

Your code works great for this case, but for future reference, is there a different way to approach it if you need to work on a complicated calculated field ?

Richard

 

[danvlas]

Yes...Include the calculation in the underlying query. Then it will return a field that you can sum directly in the report.

Regards,

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[Rickalty]

I tried going that route first, but the problem I had was getting the calculation to include results of the sum of fields.

The decision as to whether a voyage is in tolerance or not is if the ship/shore ratio is within 0.3% of the overall ship/shore ratio. To do this, therefore, I have to select all voyages, sum the ship and shore figures and get the overall ratio, then compare that figure to the ship/shore ratio of each voyage. Then I need the report to show all voyages, the overall ratio, the figures for the in-tolerance voyages, and a calculated ratio for only the in-tolerance voyages.

 

[danvlas]

OK, then make an additional query to return only one row (the totals), include it in the definition of the query of the report and you're done...No joins are necessary, but make sure you have only one totals row...

Daniel Vlas
Systems Consultant
danvlas@yahoo.com