Calculate number of days between a time frame

[khicon73]

Hello all,
I don't know where to start, please help. I don't know if it's possible to calculate the average number of days between a start and end day.

Below is my query's result:
[Enter Begin CheckedDate] = "01/01/08" and [Enter End CheckedDate] = 06/20/08]

ID CheckedDate CheckedYesNo
15 01/12/2008 0
15 04/27/2008 -1
15 05/15/2008 -1
15 06/12/2008 0
27 01/01/2008 -1
27 03/15/2008 0
27 05/18/2008 0
27 06/19/2008 -1

Is it possible to calculate the average number of days between a CheckedDate and a CheckedDate with "CheckedYesNo" = "0" during this time frame (01/01/08 -06/20/08)? I use access 2003.

If not, can I get an average number of days of each individual ID? Just want to show the total on the report.

Please help, I'm very appreciated...
Again, thank you very much.

 

[SeeThru]

could use some dmax..

dmax("CheckedDate","mytable","ID=" & [inputID] & " and CheckedYesNo=False")-dmax("CheckedDate","mytable","ID=" & [inputID] & " and CheckedYesNo=true")



SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[jadixon]

Look up the DateDiff function - the number of days would look something like

Days_Between = DateDiff ("d", date1, date2)

where date1 and date2 are your determined dates.

 

[Golom]

Select ID, Avg(Days) As [Average Days]

From

(

Select A.ID, DateDiff("d", A.CheckedDate, MIN(B.CheckedDate)) As [Days]

From TheTable  As A INNER JOIN TheTable  As B
     ON A.ID = B.ID AND A.CheckedDate < B.CheckedDate

Group By A.ID, A.CheckedDate

) As X

Group By X.ID