Average Date

[FlyGuyTray]

I am having a bit of trouble returning the average creation date for open items in my database.. I thought it easy to create a query where i select "AVG" on the "DateCreated" field. But instead of returning a date, it returns a number...

 

[Alansidman]

Try formatting the output in your query as a date in the properties for the query. I took the value and popped it into an excel sheet and formatted it as a date and it produced 10/14/2011. Is that the result you were looking for? If so, then it is just a format issue in Access.

 

[FlyGuyTray]

HOw would i do that?

 

[vbaInet]

There's nothing wrong with the output. A date is simply a Long number and if you want the average it will return the average based on the Long. If you think about it, what is the average between today and tomorrow??

 

[vbaInet]

Forgot to mention, what AlanSidman is saying is:

CDate(Avg([[B]DateField[/B]]))

Then change Avg in the Total row to Expression.

 

[FlyGuyTray]

Very Good point vbaInet! and thanks a bunch folks..

 

[datAdrenaline]

>> A date is simply a Long number <<

As an FYI. A VBA Date and the Date/Time datatype in Jet/ACE is an 8 byte floating point number (a Double) with the whole number representing days from a base date of 12/30/1899 (US format). The decimal portion represents the portion of a day passed midnight. A decimal portion of 0 is for 12:00 AM; 0.5 is 12:00 PM, etc.