Computing Average of Field of Dates in Days

[halt4814]

I have a query that selects records in a certain date range. One of the fields that it outputs is a date in "MM/DD/YYYY" format. What I want my form to output is the average age of that field, but in "days". I want to use the DAvg function, but it outputs some huge number. Help please!



The function I used is

=DAvg("<date field name>","<query name>")

[orange]

Can you show sample data of what you are trying to do, along with what you would consider an Average?

[halt4814]

For example,

I have a query that outputs Order Number and Date fields.
The data is...

1253 10/1/2012
3245 10/10/2012
4236 10/20/2012

What I want to accomplish is getting the average age of all those orders. In this case, it would be 49.67 days because from today (11/29/2012), the first order is 59 days old, the second is 50, and the last is 40 days old. How do I code my vba, so that a textbox on my Access form displays that number.

I tried =DAvg("<date field name>","<query name>"), but it displays some huge number...

[halt4814]

The full code I used was

Textbox15.value = DAvg("<date field name>","<query name>")

[orange]

You could look at the dateDiff function to get the number of days since today, then manipulate the result.

I'm not sure what the Avg you're suggesting actually means --- different Orders, different Orderdates???

[halt4814]

The avg of the order dates

[orange]

No it isn't the average Order date. I think what you are asking for is the
Average number of Days of Outstanding Orders

And I suggest you use the datediff function
http://www.techonthenet.com/access/f...e/datediff.php


The arithmetic is : (untested)

As of today, the average number of days of Outstanding orders =
Sum (datediff("d","OrderDate","Date")/ (Number of Outstanding Orders)

[RayMilhon]

The reason that it is showing a huge number is that a date is stored as a number for example 01/01/1900 is actually stored in the db as 1 or the number of days since 12/31/1899 the date datatype is how the number is displayed. So DAVG(datefield,query) assuming the dates are in 2012 is the average of (365*112)+(number of days since jan 1 this year for the date in your db) as stated you need to use the datediff function to get the number of days between the datefield and todays date. Then use the DAVG on that calculation