query that calculates the difference between two dates generated b

[Guest]

I am struggling with the development of a formula that calculates the no of
days between two dates that are both selected as a result of IIF statements.
The formula I have written is:

=(IIf([End Date] Is Null Or [End Date]>=[Period End Date],[Period End
Date],[End Date]))-(IIf([Start Date]<[Period Start Date],[Period Start
Date],[Start Date]))

[End Date] and [Start Date] are fields in a table. [Period End Date] and
[Period Start Date] are entered by the user when the query is run.

The formula works correctly when Both (i) End Date is not blank and is
ealier than the Period End Date and (ii) when Start Date is later than
Period Start Date but not under any other circumstances.

Any advice would be gratefully received.

Thanks

 

[kingston via AccessMonster.com]

Try creating a logic table to clear things up. Also, your statement cannot
work in all cases (e.g. [End Date] is null and [Period End Date] is null or
not a real date, etc.). So, to develop a really robust query, you need to
trap bad dates entered by the user (e.g. 2/30/2006) and you need to clearly
determine what results you want in every combination.

First, I'm assuming that the fields [End Date] and [Start Date] will contain
no bad data. I.e. all entries are real dates and [End Date] is always >=
[Start Date].
Second, to trap bad entry dates and null entries, use the functions Nz() and
IsDate(). I suggest you default to today, yesterday, or whatever is
appropriate when you encounter a null or bad date.
Third, construct your nested IIF statements according to your logic table and
replace the user entered data with whatever you come up with in part two.

I am struggling with the development of a formula that calculates the no of
days between two dates that are both selected as a result of IIF statements.
The formula I have written is:

=(IIf([End Date] Is Null Or [End Date]>=[Period End Date],[Period End
Date],[End Date]))-(IIf([Start Date]<[Period Start Date],[Period Start
Date],[Start Date]))

[End Date] and [Start Date] are fields in a table. [Period End Date] and
[Period Start Date] are entered by the user when the query is run.

The formula works correctly when Both (i) End Date is not blank and is
ealier than the Period End Date and (ii) when Start Date is later than
Period Start Date but not under any other circumstances.

Any advice would be gratefully received.

Thanks

 

[Guest]

Please 'splain the result you are trying to achieve in terms of start, end,
periodStart, periodEnd

 

[Guest]

Bruce,

What I am trying to do is to create a way of calculating the number of days
to pay for a medical treatment at a daily rate during a reporting period - of
say one month. So the possible scenarios that the formula needs to provide
for are:

1) A treatment could have commenced before the reporting period and not have
been completed by the end of the month - in which case we would pay for a
full month

2) The treatment could have started after the period start date and not have
finished

3) The treatment could have started before the period and finished during
the period

4) The treatment could have started and finished within the period - less
than 31 days

5) The treatment could have finished before the reporting period starts- 0
DAYS

6) The contract could have started after the reporting period ends - 0 DAYS

Thanks

ANDREW

Please 'splain the result you are trying to achieve in terms of start, end,
periodStart, periodEnd

I am struggling with the development of a formula that calculates the no of
days between two dates that are both selected as a result of IIF statements.
The formula I have written is:

=(IIf([End Date] Is Null Or [End Date]>=[Period End Date],[Period End
Date],[End Date]))-(IIf([Start Date]<[Period Start Date],[Period Start
Date],[Start Date]))

[End Date] and [Start Date] are fields in a table. [Period End Date] and
[Period Start Date] are entered by the user when the query is run.

The formula works correctly when Both (i) End Date is not blank and is
ealier than the Period End Date and (ii) when Start Date is later than
Period Start Date but not under any other circumstances.

Any advice would be gratefully received.

Thanks

 

[Guest]

Here's your logic table
start = start of treatment
begin = beginning of period
finish = finish of treatment
end = end of period

start<=begin & finish <= end : DateDiff(begin, finish)
start<=begin & (finish = NULL OR finish >= end) : DateDiff(begin,end)
start>=begin & (finish = NULL OR finish >= end) : DateDiff(start,end)
start>=begin & finish <= end : DateDiff(start,finish)
start > end OR finish < begin : 0

Bruce,

What I am trying to do is to create a way of calculating the number of days
to pay for a medical treatment at a daily rate during a reporting period - of
say one month. So the possible scenarios that the formula needs to provide
for are:

1) A treatment could have commenced before the reporting period and not have
been completed by the end of the month - in which case we would pay for a
full month

2) The treatment could have started after the period start date and not have
finished

3) The treatment could have started before the period and finished during
the period

4) The treatment could have started and finished within the period - less
than 31 days

5) The treatment could have finished before the reporting period starts- 0
DAYS

6) The contract could have started after the reporting period ends - 0 DAYS

Thanks

ANDREW

Please 'splain the result you are trying to achieve in terms of start, end,
periodStart, periodEnd

I am struggling with the development of a formula that calculates the no of
days between two dates that are both selected as a result of IIF statements.
The formula I have written is:

=(IIf([End Date] Is Null Or [End Date]>=[Period End Date],[Period End
Date],[End Date]))-(IIf([Start Date]<[Period Start Date],[Period Start
Date],[Start Date]))

[End Date] and [Start Date] are fields in a table. [Period End Date] and
[Period Start Date] are entered by the user when the query is run.

The formula works correctly when Both (i) End Date is not blank and is
ealier than the Period End Date and (ii) when Start Date is later than
Period Start Date but not under any other circumstances.

Any advice would be gratefully received.

Thanks

 

[Guest]

Thanks Bruce, have got it sorted!

Here's your logic table
start = start of treatment
begin = beginning of period
finish = finish of treatment
end = end of period

start<=begin & finish <= end : DateDiff(begin, finish)
start<=begin & (finish = NULL OR finish >= end) : DateDiff(begin,end)
start>=begin & (finish = NULL OR finish >= end) : DateDiff(start,end)
start>=begin & finish <= end : DateDiff(start,finish)
start > end OR finish < begin : 0

Bruce,

What I am trying to do is to create a way of calculating the number of days
to pay for a medical treatment at a daily rate during a reporting period - of
say one month. So the possible scenarios that the formula needs to provide
for are:

1) A treatment could have commenced before the reporting period and not have
been completed by the end of the month - in which case we would pay for a
full month

2) The treatment could have started after the period start date and not have
finished

3) The treatment could have started before the period and finished during
the period

4) The treatment could have started and finished within the period - less
than 31 days

5) The treatment could have finished before the reporting period starts- 0
DAYS

6) The contract could have started after the reporting period ends - 0 DAYS

Thanks

ANDREW

Please 'splain the result you are trying to achieve in terms of start, end,
periodStart, periodEnd

:

I am struggling with the development of a formula that calculates the no of
days between two dates that are both selected as a result of IIF statements.
The formula I have written is:

=(IIf([End Date] Is Null Or [End Date]>=[Period End Date],[Period End
Date],[End Date]))-(IIf([Start Date]<[Period Start Date],[Period Start
Date],[Start Date]))

[End Date] and [Start Date] are fields in a table. [Period End Date] and
[Period Start Date] are entered by the user when the query is run.

The formula works correctly when Both (i) End Date is not blank and is
ealier than the Period End Date and (ii) when Start Date is later than
Period Start Date but not under any other circumstances.

Any advice would be gratefully received.

Thanks

 

[Guest]

Thanks for the help - all sorted now!!

Try creating a logic table to clear things up. Also, your statement cannot
work in all cases (e.g. [End Date] is null and [Period End Date] is null or
not a real date, etc.). So, to develop a really robust query, you need to
trap bad dates entered by the user (e.g. 2/30/2006) and you need to clearly
determine what results you want in every combination.

First, I'm assuming that the fields [End Date] and [Start Date] will contain
no bad data. I.e. all entries are real dates and [End Date] is always >=
[Start Date].
Second, to trap bad entry dates and null entries, use the functions Nz() and
IsDate(). I suggest you default to today, yesterday, or whatever is
appropriate when you encounter a null or bad date.
Third, construct your nested IIF statements according to your logic table and
replace the user entered data with whatever you come up with in part two.

I am struggling with the development of a formula that calculates the no of
days between two dates that are both selected as a result of IIF statements.
The formula I have written is:

=(IIf([End Date] Is Null Or [End Date]>=[Period End Date],[Period End
Date],[End Date]))-(IIf([Start Date]<[Period Start Date],[Period Start
Date],[Start Date]))

[End Date] and [Start Date] are fields in a table. [Period End Date] and
[Period Start Date] are entered by the user when the query is run.

The formula works correctly when Both (i) End Date is not blank and is
ealier than the Period End Date and (ii) when Start Date is later than
Period Start Date but not under any other circumstances.

Any advice would be gratefully received.

Thanks