date type data

[Faisal]

Dears

While we making a query by using query wizard' there is a option to get
sum,min ,max,avg in summary option if the data is in number type.

Any body can sujest any logic to get max and min value if the data type is
in date field while we making a query by using query wizard


any help please


Regards
Faisal
Riyadh

 

[John Spencer]

As far as I know there is no way to do this using the query wizard.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Ron2006]

Just use max and min.

Max will get the most recent date (or farthest in future)
Min will get the date that is farthest in the past.

Ron

 

[Ron2006]

My last response did not seem to post.

Just use MIN and MAX (Date is stored as a number so will work fine)


Max will get the most recent (or one farthest in future)

Min will get the date farthest in the past.

I tried it and it works just fine.

Ron

 

[Faisal]

Dear ron

how we can convert date filed to number to achive this

 

[Ron2006]

It already is really a number.

Just write the query and use the field as if it were a number. No
other steps are necessary.

A date is stored internally as a number with a decimal.

The whole number part (to the left of the decimal) is the number of
day that the date is since I think it is something like 01/01/0001

and the decimal part is the probably the nanoseconds since midnight
which is 0.

That is why you can say Date() + 1 and it will return tomorrow's
date or Date()-1 and it will return yesterday's date.

But if you want to play with hours you usually are safer using the
DateAdd function.

Ron.

 

[John W. Vinson]

It already is really a number.

Just write the query and use the field as if it were a number. No
other steps are necessary.

A date is stored internally as a number with a decimal.

The whole number part (to the left of the decimal) is the number of
day that the date is since I think it is something like 01/01/0001

12/30/1899 as a matter of fact. It's a historical accident that it's not the
apparently more logical 12/31/1899, which would make 1 correspond to January
1, 1900; apparently Lotus 123's date convention was created with the erroneous
assumption that 1900 was a leap year, and was off by a day; the Access (and
Excel, more to the point) convention was set to match, one day offset and all.

and the decimal part is the probably the nanoseconds since midnight
which is 0.

Wrong. It's fractions of a day. 0.5 is noon, 0.75 is 6 pm.

That is why you can say Date() + 1 and it will return tomorrow's
date or Date()-1 and it will return yesterday's date.

But if you want to play with hours you usually are safer using the
DateAdd function.

Definitely.

 

[Ron2006]

Thank you for the corrections, John.

I was not sure about the exact date and did not realize that it was a
fraction part for the time - I had seen the decimal part of the number
from time to time but never had seen an explanation nor tried to
derive it (or I guess saw it for a time that would make it obvious
that it was a fraction.)

I will save the explanation in my growing DB of Access explanations
and trivia.

I appreciate the history lesson.

Ron

 

[John W. Vinson]

I appreciate the history lesson.

The question "Why is the Access date value zero point set to December 30
instead of December 31" is a good trivia question to see who's really an
Access old-timer!