How do I add an 'isnull' statement to the formula below?

spectrum17
spectrum17 used Ask the Experts™
on
I need the following formula to come up with a result of 0 if there aren't any records in the 'Leave_Taken' database.

Any ideas how to do this?


if date({Leave_Taken_csv.Leave Start Date}) in
{?Start Date}to datetime({@annivdate}) then {Leave_Taken_csv.Hours Taken} else
if date ({Leave_Taken_csv.Leave End Date}) in
{?Start Date}to datetime({@annivdate}) then {Leave_Taken_csv.Hours Taken} else
else
 0

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
I assume if the startdate has a date then enddate will also have a date?

Try
If IsNull({Leave_Taken_csv.Leave Start Date})  then
    0
else if date({Leave_Taken_csv.Leave Start Date}) in {?Start Date}to datetime({@annivdate}) then
    {Leave_Taken_csv.Hours Taken}
else If IsNull({Leave_Taken_csv.Leave End Date})  then
    0
else if date ({Leave_Taken_csv.Leave End Date}) in {?Start Date}to datetime({@annivdate}) then
    {Leave_Taken_csv.Hours Taken} else
else
    0

mlmcc

Author

Commented:
Perfect. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial