[RESOLVED] Calculate Fields in Domain Aggregate Functions using DateDiff

[colecole]

I have a table tblDocInfo which I made a continuous form with the fields
PurchaseOrderApprovalDate and ActualDeliveryDate and primary key docID (autonumber). I have a calculated control difTargetOutputStep1 with Control Source=DateDiff("d",[PurchaseOrderApprovalDate],[ActualDeliveryDate])

I want to calculate the average of these values on the form (will be all values ActualDeliveryDate-PurchaseOrderApprovalDate for the tblDocInfo).

In summary: I have a field showing the number of days between the ActualDeliveryDate and PurchaseOrderApprovalDate. I would like the user to be able to compare this value for each docID to the average of all of the days between the ActualDeliveryDate and PurchaseOrderApprovalDate.

For some reason I am having a hard time with this....any suggestions?

[Siddharth Rout]

I am not sure if there is a shorter way of doing this but this logic is what I could think of at the moment...

1) Declare an Array. The size of the array will be the tables record count.
2) Loop through each record and find ActualDeliveryDate-PurchaseOrderApprovalDate and store it in the above array.
3) Find Average using the values stored in the array.

[anhn]

On Form Header or Footer, create a control TextBox with ControlSource as:
=Avg([ActualDeliveryDate]-[PurchaseOrderApprovalDate])
or
=Avg(DateDiff("d",[PurchaseOrderApprovalDate],[ActualDeliveryDate]))

[colecole]

Thank you! I was using DAvg and it was giving me an error #Name?. ugh.... Thanks again.