Calculate Number of Days

[Steve]

I'm trying to calculate the number of days someone attended our program. I
have their entry date and exit date. I would like to calculate the number
of days somenone was in our program for a period of time.

Example:
Someone enters our program on May 1, 2002 and exits our program on July 31,
2002. Our fiscal year runs July 1 - June 30. So during our fiscal year
2002-2003 they were in our program 31 days. But how do I make Access give
me the answer when it's not so obvious?

TIA Steve

 

[Allen Browne]

You want the date difference between:
the larger of their start date and the year's start date,
and
the lesser of their end date and the year's end date.

You need to have some way to specify which fiscal year you are asking about.
This example pops up a box asking for the YearStartDate. It assumes you have
fields named Started and Ended for when the person entered and left the
program. Try typing it into a fresh column of the Field row in query design:

Days: DateDiff("d", IIf([Started] > [YearStartDate], [Started],
[YearStartDate]),
IIf([Ended] < DateAdd("yyyy", 1, [YearStartDate]) - 1, [Ended],
DateAdd("yyyy", 1, [YearStartDate]) - 1))

That will give nonsense results for entries that do not overlap the fiscal
year at all.

It's a really good idea to declare the YearStartDate as a parameter, so
Access knows to treat it as a Date/Time type: Parameters on Query menu.

 

[fredg]

I'm trying to calculate the number of days someone attended our
program. I have their entry date and exit date. I would like to
calculate the number of days somenone was in our program for a
period of time.

Example: Someone enters our program on May 1, 2002 and exits our
program on July 31, 2002. Our fiscal year runs July 1 - June 30.
So during our fiscal year 2002-2003 they were in our program 31
days. But how do I make Access give me the answer when it's not so
obvious?

TIA Steve

Steve,

If you wish the days just for fiscal 2002-2003, (i.e. after 6/30/2002)
then:

=DateDiff("d",#6/30/2002#,[EndDate])

or you can use a prompt to enter the wanted year in:

=DateDiff("d",DateSerial([Enter year],6,30),[ADate])

and re-use this each year without having to re-write the expression.

 

[Francesca Sullivan]

Days Attended:
DateDiff("d",[StartDate],IIf(IsNull([EndDate]),Date(),[EndDate]))

Francesca

 

[Victor Delgadillo]

One simple way to detect the Fiscal Year is to find the month. For example,
our fiscal year goes from Oct 1 through Sep 30. For a given date:
FYear = Iif(month([date_entered])<10,Year([date_entered]) -
1,Year([date_entered]))
(for other FY beginnings, adjust the constant 10 on the line.)
Then to calculate days to FY:
EFDate = CDate("9/30/" & Fyear)
If EFDate < EndingDate Then
EFDate = EndingDate
DateDiff("d",[StartDate], EFDate) ' this will give the number of days
either to the second date or the end of fiscal year

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_

Days Attended:
DateDiff("d",[StartDate],IIf(IsNull([EndDate]),Date(),[EndDate]))

Francesca

I'm trying to calculate the number of days someone attended our program. I
have their entry date and exit date. I would like to calculate the number
of days somenone was in our program for a period of time.

Example:
Someone enters our program on May 1, 2002 and exits our program on July 31,
2002. Our fiscal year runs July 1 - June 30. So during our fiscal year
2002-2003 they were in our program 31 days. But how do I make Access give
me the answer when it's not so obvious?

TIA Steve