another multiple IIf

[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

 

[Andy Hull]

Hi

The following does the trick...

=IIf(([Admit_Date] > [ctl_Ending_Date]) Or ([Discharge_Date] <
[ctl_Beginning_Date]), 0, DateDiff("d", IIf([Admit_Date] >
[ctl_Beginning_Date], [Admit_Date], [ctl_Beginning_Date]),
IIf([Discharge_Date] < [ctl_Ending_Date], [Discharge_Date],
[ctl_Ending_Date])) + 1)

The first part checks to see if the person's stay isn't outside the period
completely so you may not need that bit if your query/report/form has already
filtered out these.

Regards

Andy Hull

 

[John Spencer]

Replied to your earlier post.

Please be patient. The responders in these newgroups are volunteers. It is
not unusual for a posting to take 24 hours to get a response, although the
normal response time is usually on the order of an hour or so. I saw your
original post last night, but didn't understand it until I reread it this
morning.

I hope the solution I posted this morning will work for you.

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