×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

formula not completely working
3

formula not completely working

formula not completely working

(OP)
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)

RE: formula not completely working

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).

RE: formula not completely working

(OP)
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.

RE: formula not completely working

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.

RE: formula not completely working

2
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

RE: formula not completely working

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

thumbsup2

RE: formula not completely working

I forgot about the NULL check. I should know better smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close