Report Average or Sum

[AlCamp]

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by the
query.

The form is frmDialog, and the Option Frame on that form is [AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with this as a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)
SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum = 1,[DefectCount],Avg([DefectCount])))
I get a "Can't use aggregate function" (the AVG in this calculation) error.

Any help will be appreciated.

As always... Thanks,
Al Camp

 

[AlCamp]

Typo... this portion of the post should read...

Then I tried adding the Average portion... (all on one line)
SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum = 2,[DefectCount],Avg([DefectCount])))
I get a "Can't use aggregate function" (the AVG in this calculation)
error.

(AND Forms!frmDialog!AverageOrSum = 2 --- instead of 1)

Al Camp

On a Totals query for a report, I want the user to be able to check an
option box on a Dialog form and have an Average, or Sum returned by the
query.

The form is frmDialog, and the Option Frame on that form is [AverageOrSum]
and it has 2 checkbox elements...
[Average]=1 and [Sum]=2

At present, I have the Sum portion of this problem working with this as a
calculated column...
SumOf1s : Sum(IIf([DefectID]=1,[DefectCount],0)))

Then I tried adding the Average portion... (all on one line)
SumOrAvg1s : Sum(IIf([DefectID]=1
AND Forms!frmDialog!AverageOrSum = 1,[DefectCount],Avg([DefectCount])))
I get a "Can't use aggregate function" (the AVG in this calculation)
error.

Any help will be appreciated.

As always... Thanks,
Al Camp