Calculating dates between to date ranges

[raftingdon2]

Calculating dates between TWO date ranges:

I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.

Example:

test table:

Date ID # Name
1/18/07 1234567 Smith
1/20/07 1234567 Smith
1/23/07 1234567 Smith


Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?

(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)


Don

 

[Guus2005]

Select the record you need with the build in DMax and DMin functions
Datediff the result. You can do this in VBa but also in a query.

your query won't look pretty.

 

[raftingdon2]

Select the record you need with the build in DMax and DMin functions
Datediff the result. You can do this in VBa but also in a query.

your query won't look pretty.

Thank you very much, that did the trick. I'm having one more problem that sort of relates to this. It may be very simple and smacking me in my face.
I've been able to get the difference in the date by:

Max Date: DMax("date","test","[ID Number]=1234567")
Min Date: DMin("date","test","[ID Number]=1234567")
Difference: DateDiff("d",[min date],[max date])

I have many difference ID numbers to do this for. If I take out the Where clause (Id = 1234567) it performs the function for all dates in the table from Min to Max. I want it to give me a different difference for each individual ID number (without having to rerun the query for each specific ID number). I'm knocking on the door (almost got it), but i'm knocking on the neighbors door.

Thanks again for help.

 

[Jon K]

Try this Totals Query.

SELECT [ID #], [Name], Max([Date]) AS MaxDate, Min([Date]) AS MinDate, MaxDate-MinDate AS Diff
FROM [Test Table]
GROUP BY [ID #], Name;

Note: Date is a function name and # is a special character. It's better not to use them in field names.
.