Finding DateDiff between Min(Date) & Max(Date)

[londonkiwi]

Firstly, I have checked through the Tek-Tips dbase to see if any answers have been provided previously - none?

I have a "Date" Field in a Table. In a query, I have found the min(DATE) and max(Date) dates eg 18/09/2001 and 27/09/2001. How do you then find the number of days between min(DATE) and max(DATE)

SQL as below

SELECT Min(IT.DATE) AS DATE_MIN, Max(IT.DATE) AS DATE_MAX, DateDiff("y",[min(Date)],[max(Date)]) AS DaysElapsed
FROM IT;

cheers
thanks in advance
The "DaysElapsed" fails.

 

[tomschafer]

Try this...

SELECT Min(IT.DATE) AS DATE_MIN, Max(IT.DATE) AS DATE_MAX, DateDiff("d",Min([Date]),Max([Date])) AS DaysElapsed
FROM IT;

 

[mymou]

Hi

Are you sure you want to do this through SQL?

If this is just something to be displayed on a form - then use Dmax/Dmin to pull out the values and then datediff to find the difference.

If you like formulas then use:

=DateDiff("d",DMin("[FieldName]","[TableName]&quot,DMax("[FieldName]","[TableName]&quot)

Is this what you want?


Stew "Even a stopped clock tells the right time twice a day."

 

[londonkiwi]

Great guys - thnaks for both the replies. Worked a treat

 

[londonkiwi]

Thanks everyone - works well.