Calculate averages but exclude "null" months and include when a "0" is shown

[neelsfer]

Adezii kindly helped me with the following code many months ago when i wanted to exclude months that showed a "0" value issued to patients for that month.
It now works like this: when the supplier has no stock and nothing was issued to patients during a month, then we are going to add 99999 (or should we make it blank perhaps? i will prefer that) in the quantity field.

If nothing was issued to patients during a month but we had stock on hand, then i will use "0" in the quantity field.
I need to calculate the average using this criteria for a period

Result: FormatNumber((DSum("[Quantity]","tblAcquire","[Quantity]>0"))/(DCount("[Month]","tblAcquire","[Quantity]>0")),2)

month     Quantity
1            20
2            99999  or leave blank?
3             0
4             18
5             99999  or leave blank?

Please assist

[zmbd]

Personally, I would leave the field empty (blank) so if you used a totals query, the average() will not include the empty fields in the calculation. Otherwise you will have to have a ton of code to handle the 99999.

The zero would be counted and included in the Average() in a totals query.

[neelsfer]

thx zmbd.This is then straight forward. I will add the date but leave the quantity blank. As is understand it, i dont have to include anything to ignore blank fields when calculating averages?

[zmbd]

You shouldn't have to add anything.