I have searched on Sum and DSum but cannot find anything that is relevant to my problem.
I have a report which is derived form a form/subform.
The main form has a unquie ID number amongst other fields, the subform has grade, volume and price fields among others.
A report is derived from a query that only shows those records where the ID matches in both main and subform, this part works fine.
My problem is that I want to sum the volumes but only if the grade is a certain value e.g. "P" in my report.
I have tried an unbound textbox in the report with the formula =Sum(IIf([Grade]="P",Forms!PropertyLogDocketMainForm!qryLogDocketSubForm.Form!VOL,0))
but this gives me a figure that does not add up to anything in the subform volume fields.
My other problem is that I need to sum the prices of only the "P" grade items in another unbound textbox on the report, I have tried this formula - =Sum(IIf([Grade]="P",Forms!PropertyLogDocketMainForm!qryLogDocketSubForm.Form![Pole Price],0))
but again I get some weird amount that doesn't resemble any of the data in the subform. I think that the problem with this field is that in some instances a row of data in the subform may not have a price and there are blanks in that column e.g.
Volume Grade Price
1.256 P 35.00
2.589 O
1.025 P 86.00
As you can see there are gaps in the column, does this affect things (I know it does in Excel, but unsure in Access)
Would DSum be better than Sum?
Any suggestions would be greatly appreciated.
Cheers
optidisk
I have a report which is derived form a form/subform.
The main form has a unquie ID number amongst other fields, the subform has grade, volume and price fields among others.
A report is derived from a query that only shows those records where the ID matches in both main and subform, this part works fine.
My problem is that I want to sum the volumes but only if the grade is a certain value e.g. "P" in my report.
I have tried an unbound textbox in the report with the formula =Sum(IIf([Grade]="P",Forms!PropertyLogDocketMainForm!qryLogDocketSubForm.Form!VOL,0))
but this gives me a figure that does not add up to anything in the subform volume fields.
My other problem is that I need to sum the prices of only the "P" grade items in another unbound textbox on the report, I have tried this formula - =Sum(IIf([Grade]="P",Forms!PropertyLogDocketMainForm!qryLogDocketSubForm.Form![Pole Price],0))
but again I get some weird amount that doesn't resemble any of the data in the subform. I think that the problem with this field is that in some instances a row of data in the subform may not have a price and there are blanks in that column e.g.
Volume Grade Price
1.256 P 35.00
2.589 O
1.025 P 86.00
As you can see there are gaps in the column, does this affect things (I know it does in Excel, but unsure in Access)
Would DSum be better than Sum?
Any suggestions would be greatly appreciated.
Cheers
optidisk
