formula not completely working 3

[Jedderm]

using 3/1/19 as beginadmitdate and 3/31/19 as Endadmitdate

all the instances work when dates are outside of those ranges except the last array.
It gives me the total number of days in a field if ex: 2/19/19 to 4/15/19 is the dates in the fields themselves which is 56.
What am I missing in this last part of the array?


if isnull ({history_bed_assignment.end_date_of_bed_assignment}) or
{history_bed_assignment.end_date_of_bed_assignment}>{?EndAdmitDate}and
{history_bed_assignment.date_of_bed_assignment}>={?BeginAdmitDate} then
Datediff("D",{history_bed_assignment.date_of_bed_assignment},{?EndAdmitDate}+1)
else
if ({history_bed_assignment.date_of_bed_assignment})<{?BeginAdmitDate} and
{history_bed_assignment.end_date_of_bed_assignment} in {?BeginAdmitDate} to {?EndAdmitDate} then
Datediff("D",{?BeginAdmitDate},{history_bed_assignment.end_date_of_bed_assignment}+1)
else
if ({history_bed_assignment.date_of_bed_assignment})<={?EndAdmitDate} and
{history_bed_assignment.end_date_of_bed_assignment}>={?BeginAdmitDate} then
Datediff("D",{history_bed_assignment.date_of_bed_assignment},{history_bed_assignment.end_date_of_bed_assignment}+1)
else
if {history_bed_assignment.date_of_bed_assignment}<{?BeginAdmitDate}and
isnull ({history_bed_assignment.end_date_of_bed_assignment} )or
{history_bed_assignment.end_date_of_bed_assignment} > {?EndAdmitDate}
then
Datediff("D",{?BeginAdmitDate},{?EndAdmitDate}+1)

 

[kray4660]

I am little confused since the last 'array' (if statement) does a date difference of the parameter values (in this case the difference between 3/1/19 and 3/31/19 which is 31 days).

 

[Jedderm]

and that's what it's supposed to do, but, it's not if the date and end date of bed assign is not in the parameter range.

 

[kray4660]

if {history_bed_assignment.date_of_bed_assignment}<{?BeginAdmitDate}and
isnull ({history_bed_assignment.end_date_of_bed_assignment} )or
{history_bed_assignment.end_date_of_bed_assignment} > {?EndAdmitDate}

So according to this statement. If the date_of_bed_assignment is less than the BeginAdmitDate and end_date_of_bed_assignment is null or end_date_of_bed_assignment is greater than the EndAdmitDate then the statement will be true. My question (did not want to dig through the rest of the code), does it ever get that far. The only way to know is to break out each if statement in test formulas and see when each one becomes true.

 

[lbass]

Two issues:
1- If you are checking whether a field is null, you must do the null check BEFORE the field is referenced anywhere else in the formula.
2- With "or" statements, you must set off clauses with parens to eliminate confusion about what clauses go with what part of the "or" statement. I set this up differently and didn't need to use or statements.

Rewrite your formula like this:

if isnull ({history_bed_assignment.end_date_of_bed_assignment} ) then
(
if {history_bed_assignment.date_of_bed_assignment}<{?BeginAdmitDate} then
Datediff("D",{?BeginAdmitDate},{?EndAdmitDate}+1) else
if {history_bed_assignment.date_of_bed_assignment}>={?BeginAdmitDate} then
Datediff("D",{history_bed_assignment.date_of_bed_assignment},{?EndAdmitDate}+1)
) else

if {history_bed_assignment.end_date_of_bed_assignment}>{?EndAdmitDate} then
(
if {history_bed_assignment.date_of_bed_assignment}>={?BeginAdmitDate} then
Datediff("D",{history_bed_assignment.date_of_bed_assignment},{?EndAdmitDate}+1)
else
if {history_bed_assignment.date_of_bed_assignment}<{?BeginAdmitDate}
Datediff("D",{?BeginAdmitDate},{?EndAdmitDate}+1)
) else

if {history_bed_assignment.end_date_of_bed_assignment} in {?BeginAdmitDate} to {?EndAdmitDate} then
(
if ({history_bed_assignment.date_of_bed_assignment})<{?BeginAdmitDate} then
Datediff("D",{?BeginAdmitDate},{history_bed_assignment.end_date_of_bed_assignment}+1)
else
if {history_bed_assignment.date_of_bed_assignment})>= {?BeginAdmitDate} and
{history_bed_assignment.date_of_bed_assignment} < {?EndAdmitDate}+1 then
Datediff("D",{history_bed_assignment.date_of_bed_assignment},{history_bed_assignment.end_date_of_bed_assignment}+1)
)

-LB

 

[Jedderm]

LB...this works
Thank you thank you thank you...
those nulls will throw everything out of whack.

 

[kray4660]

I forgot about the NULL check. I should know better