I'm developing a report that will select records based on several if/then/else statements. What is happening is that the formula seems to be calculating the dates correctly, however the report shows records where the results (dates) are blank because a portion of the criteria is not met. I do not wish to see these records, and I've run out of ideas on how to suppress them if blank.
So far, I've dropped the formula into the selection expert with a "not isnull ({@scheduledate})" formula, but it still returned those records with a blank scheduled date. I've right-clicked on the field and formatted it so that it will suppress if null. I've also gone to File/Report Options and checked the Convert NULL Field Value to Default, but then what happens is that NO records are selected. I've also fiddled around with the below example of the formula until I realized that I've just been looking at this too much.
Here's an example of the formula I'm using (I hope it makes sense):
if {TYPE.DESCRIPTION} IN ["W" ,"X","Y"] and
if not isnull ({ITEM.DATE_ENTERED})then {ITEM.DATE_ENTERED}
else if {TYPE.DESCRIPTION} IN ["Z"]and if (STATE.CODE} IN
["AB","DC","GA","MD","MI","MN","MS","MO","NE","NH","NC","RI","TN","TX","VA","WV","WY"]
and not isnull ({STATUS.DATE_EXAMPLE1}) then
DateAdd ("d",10,{STATUS.DATE_EXAMPLE1})
else if {STATE.CODE} in ["AZ","CO"]
and not isnull ({STATUS.DATE_EXAMPLE2})
then DateAdd ("d",30,{STATUS.DATE_EXAMPLE2})
else if {STATE.CODE} IN
["AK","AR","CA","ID","MT","MN","OR","UT","WA"]
and not isnull ({STATUS.DATE_EXAMPLE2})
then DateAdd ("d",45,{STATUS.DATE_EXAMPLE2})
Any thoughts ? I've been struggling with this issue for days!
Thanks SO much !
So far, I've dropped the formula into the selection expert with a "not isnull ({@scheduledate})" formula, but it still returned those records with a blank scheduled date. I've right-clicked on the field and formatted it so that it will suppress if null. I've also gone to File/Report Options and checked the Convert NULL Field Value to Default, but then what happens is that NO records are selected. I've also fiddled around with the below example of the formula until I realized that I've just been looking at this too much.
Here's an example of the formula I'm using (I hope it makes sense):
if {TYPE.DESCRIPTION} IN ["W" ,"X","Y"] and
if not isnull ({ITEM.DATE_ENTERED})then {ITEM.DATE_ENTERED}
else if {TYPE.DESCRIPTION} IN ["Z"]and if (STATE.CODE} IN
["AB","DC","GA","MD","MI","MN","MS","MO","NE","NH","NC","RI","TN","TX","VA","WV","WY"]
and not isnull ({STATUS.DATE_EXAMPLE1}) then
DateAdd ("d",10,{STATUS.DATE_EXAMPLE1})
else if {STATE.CODE} in ["AZ","CO"]
and not isnull ({STATUS.DATE_EXAMPLE2})
then DateAdd ("d",30,{STATUS.DATE_EXAMPLE2})
else if {STATE.CODE} IN
["AK","AR","CA","ID","MT","MN","OR","UT","WA"]
and not isnull ({STATUS.DATE_EXAMPLE2})
then DateAdd ("d",45,{STATUS.DATE_EXAMPLE2})
Any thoughts ? I've been struggling with this issue for days!
Thanks SO much !