DAvg function in subtotals

[MEG]

I want to define subtotals (specifically averages) in a grouped report, but I want to exclude values over 199 from the calculation. It appears that DAvg should achieve this but it actually produces some very odd results (but does not give an error message). Should I be able to use DAvg to do this? If so, how?

As an alternative, I tried inserting a calculated field that returns the original value if less than 200, and NULL if the original value is 200+. This works in itself, but I then can't insert it into the AVG function and run the report (it comes up with a demand for parameters to be inserted). Should I be able to use this alternative approach? Is there a third option?

 

[vbaInet]

I want to define subtotals (specifically averages) in a grouped report, but I want to exclude values over 199 from the calculation. It appears that DAvg should achieve this but it actually produces some very odd results (but does not give an error message). Should I be able to use DAvg to do
this? If so, how?

=DAvg("IIF([SomeField] > 199, Null, 0)", "TableOrQuery")

As an alternative, I tried inserting a calculated field that returns the original value if less than 200, and NULL if the original value is 200+. This works in itself, but I then can't insert it into the AVG function and run the report (it comes up with a demand for parameters to be inserted). Should I be able to use this alternative approach? Is there a third option?

* Is the third parameter of the DAvg() function not sufficient for filtering the records you desire?

=DAvg("[SomeField]", "TableOrQuery", "[SomeOtherField] < 200")

 

[MEG]

=DAvg("IIF([SomeField] > 199, Null, 0)", "TableOrQuery")

* Is the third parameter of the DAvg() function not sufficient for filtering the records you desire?

=DAvg("[SomeField]", "TableOrQuery", "[SomeOtherField] < 200")

Thanks so much for the very quick response. This has taken me a bit further, but my problem is not solved.

My question becomes, can DAvg be used at the group level and, if so, how? Access seems to just "know" that other group level totals are to be calculated at the group level, based only on the location of the control in the group footer (or header) on the report; but this is not happening with DAvg. See below for what happened with each of your two suggestions.

If I use the first suggestion, and substitute [SomeField] for your "0" (otherwise the result would be "0"), I get a sensible-looking number, until I notice that the number is the same for all groups, which is simply not possible. By exporting to Excel, I have now established that the number coming from the code is actually the average (excluding cases over 199) for all the records - so it's giving a grand total average not an average at group level, even though the control is located in a group footer.

The second suggestion is the same as what I had been doing, or had intended to do, before my initial post. This was based on OnLine Help and a book I bought. I must have been making a minor error of some kind before because it was generating complete nonsense. Now, by pasting in your code and amending, it produces exactly the same results as your first suggestion, and again it's the same for all groups and I now realize it's actually the grand average for all groups together, not the group average.

 

[vbaInet]

I'm aware that both code lines produce the same result. The first was to answer your first question as asked. The second was a more direct and more effecient approach.

Yes of course you can get the Avg per group. Simply add another an AND to the where part of the DAvg() function which gets the current GroupID. Have you checked in the help files the syntax of DAvg?

http://www.techonthenet.com/access/functions/domain/davg.php