What do I need: multiple nested IIF? Switch()? DateDiff?

[AccessNeophyte]

Hi again,

I have a situation that may call for either a multiple nested IIF, or
a Switch(), or a DateDiff, something else I haven't even heard of.
Basically, I need to get the number of days during a given month that
a given client was in residence.

Here's what I need the contol in my report to show:

if [Admit Date]>=[Beginning Date],
then [Ending Date]-[Admit Date]

if [Discharge Date]<=[Ending Date],
then [Discharge Date]-[Beginning Date]

if [Discharge Date]<=[Ending Date] AND [Admit Date]=>[Beginning Date],
then [Discharge Date]-[Admit Date]

if [Discharge Date]>[Ending Date] AND [Admit Date]<[Beginning Date],
then [Ending Date]-[Beginning Date]

Beginning Date is a prompt in the query, and will usually be the first
day of the report month
Ending Date is a prompt in the query, and will usually be the last day
of the report month
Admit Date and Discharge Date are fields from the table that the
query ... um ... queries. ;o}

Thanks in advance!!!
Access Neophyte

 

[John Spencer]

If I understand you, you want to calculate the number of elapsed days in a
period of time where you have Start, End, Admit, and Discharge dates. You
need to choose the latest date between the Start-Admit pair and the earliest
date between the End-Discharge pair in order to make your calculation.

DateDiff("d",
IIF([Start]>[Admit],[Start],[Admit]),IIF([Discharge]<=[End],[End],[Discharge]))

The above assumes that there is always an admit and discharge date. I will
assume that there is always an Admit date, but not always a discharge date.
In that case, you can use the NZ function to make sure that you have a
comparison date.

DateDiff("d",
IIF([Start]>[Admit],[Start],[Admit]),IIF(Nz([Discharge],[End])<=[End],[End],[Discharge]))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[AccessNeophyte]

If I understand you, you want to calculate the number of elapsed days in a
period of time where you have Start, End, Admit, and Discharge dates. You
need to choose the latest date between the Start-Admit pair and the earliest
date between the End-Discharge pair in order to make your calculation.

DateDiff("d",
IIF([Start]>[Admit],[Start],[Admit]),IIF([Discharge]<=[End],[End],[Discharg­e]))

The above assumes that there is always an admit and discharge date. I will
assume that there is always an Admit date, but not always a discharge date.
In that case, you can use the NZ function to make sure that you have a
comparison date.

DateDiff("d",
IIF([Start]>[Admit],[Start],[Admit]),IIF(Nz([Discharge],[End])<=[End],[End]­,[Discharge]))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi again,

I have a situation that may call for either a multiple nested IIF, or
a Switch(), or a DateDiff, something else I haven't even heard of.
Basically, I need to get the number of days during a given month that
a given client was in residence.

Here's what I need the contol in my report to show:

if [Admit Date]>=[Beginning Date],
then [Ending Date]-[Admit Date]

if [Discharge Date]<=[Ending Date],
then [Discharge Date]-[Beginning Date]

if [Discharge Date]<=[Ending Date] AND [Admit Date]=>[Beginning Date],
then [Discharge Date]-[Admit Date]

if [Discharge Date]>[Ending Date] AND [Admit Date]<[Beginning Date],
then [Ending Date]-[Beginning Date]

Beginning Date is a prompt in the query, and will usually be the first
day of the report month
Ending Date is a prompt in the query, and will usually be the last day
of the report month
Admit Date and Discharge Date are fields from the table that the
query ... um ... queries. ;o}

Thanks in advance!!!
Access Neophyte- Hide quoted text -

- Show quoted text -

Thaks so much! The expression with the NZ worked great!