Average Date

F

FlyGuyTray

Registered User.

Local time

Today, 05:25

Joined

Nov 30, 2010

Messages

38

• Oct 24, 2011

• #1

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...

 

Attachments

• 
  Average.jpg

  93.4 KB · Views: 134
• 
  output.jpg

  86.8 KB · Views: 133

Alansidman

AWF VIP

Local time

Today, 04:25

Joined

Jul 31, 2008

Messages

1,493

• Oct 24, 2011

• #2

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.

 

F

FlyGuyTray

Registered User.

Local time

Today, 05:25

Joined

Nov 30, 2010

Messages

38

• Oct 24, 2011

• #3

HOw would i do that?

 

V

vbaInet

AWF VIP

Local time

Today, 10:25

Joined

Jan 22, 2010

Messages

26,374

• Oct 24, 2011

• #4

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??

 

V

vbaInet

AWF VIP

Local time

Today, 10:25

Joined

Jan 22, 2010

Messages

26,374

• Oct 24, 2011

• #5

Forgot to mention, what AlanSidman is saying is:

Code:

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

Then change Avg in the Total row to Expression.

 

F

FlyGuyTray

Registered User.

Local time

Today, 05:25

Joined

Nov 30, 2010

Messages

38

• Oct 24, 2011

• #6

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

 

D

datAdrenaline

AWF VIP

Local time

Today, 04:25

Joined

Jun 23, 2008

Messages

697

• Oct 24, 2011

• #7

>> 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.