Oh help-Complex date formula/Results inaccurate 1

[lmorgan]

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 !

 

[dgillz]

At the very begining of your if-then-else statement, add teh following line:

If Isnull({yourdatefield}) then False else

Then get rid of the Not IsNull() tests in your code, as Nul values will never make it past the first test as outlined above. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[lmorgan]

Thank you for responding to my inquiry. I do, however, need a slight bit of clarification concerning your solution of If Isnull({yourdatefield}) then False else .

Where you have ({yourdatefield]), I'm wondering which date field should be included here. The formula results could come from one of three table/field sources.

I rewrote the formula as follows (using actual table/field names) and am getting a boolean error.

if isnull ({FORECLOSURE.JUDG_ENTERED}) then false
else if {FCL_TYPE_LU.DESCRIPTION} IN ["Judicial" ,"Executory
Process","Strict"]then {FORECLOSURE.JUDG_ENTERED}

else if isnull ({FORECLOSURE.ATRN_DATE_REFER}) then false
else if {FCL_TYPE_LU.DESCRIPTION} IN ["Non Judicial (POS)"]
and {PROPERTY_INFO.STATE} IN
["AL","DC","GA","MD","MI","MN","MS","MO","NE","NH","NC","RI","TN","TX","VA","WV","WY"]
then DateAdd ("d",10,{FORECLOSURE.ATRN_DATE_REFER})

else if isnull ({FORECLOSURE.COMPLAINT_FILED}) then false
else if {PROPERTY_INFO.STATE} in ["AZ","CO"]
then DateAdd ("d",30,{FORECLOSURE.COMPLAINT_FILED})

else if isnull ({FORECLOSURE.COMPLAINT_FILED}) then false
else if{PROPERTY_INFO.STATE} IN
["AK","AR","CA","ID","MT","MN","OR","UT","WA"]
then DateAdd ("d",45,{FORECLOSURE.COMPLAINT_FILED})



What am I missing ? Thanks again for all your help.

Lisa

 

[dgillz]

Lisa,

Sorry I misread your post earlier. The reason you are getting a boolean error is a record selection formula must avaluate to true or false. You are doing dateadd() functions in your second formula. This, or anyother thing that evaluates to something other than true or false is not allowed in a boolean formula.

You need separate if-then formulas to redefine and display your various dates, and keep the record selection formula a boolean formula.

Try this and post again if you have any problems Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[lmorgan]

Thanks again for your help. I wrote a formula (@nonjudicial) that calculates the three previous if/then/else that had a dateadd function in it, which works.

I then rewrote the above formula as follows:

if isnull ({FORECLOSURE.JUDG_ENTERED}) then false
else if {FCL_TYPE_LU.DESCRIPTION} IN "Judicial" ,"Executory Process","Strict"] then {FORECLOSURE.JUDG_ENTERED}
else ({@nonjudicial})

The results of the @nonjudicial formula will return a date field just like the foreclosure.judg_entered field will, but I am still getting a boolean error.

I understand what is happening, but I'm very confused as to how to correct it. I'm truly not much of a programmer so I apologize if I'm missing something relatively easy.

Any other insight you can give me is very appreciated.

Thanks so much
Lisa

 

[dgillz]

There is nothing wrong with using dateadd in a formula, but a record selection formula MUST evaluate to true or false.

Dateadd("d",10,{datefield})=Date(2002,3,15) is an example of a boolean formula. The Dateadd() expression either IS or IS NOT equal to 3/15/2000. This formula will return True or False and is acceptable in a record selection formula.

Your formula DOES NOT evaluate to True or False, and therefore you will ALWAYS get a boolean error on it if it is used as a record selection formula.

If you want to conditionally perform a dateadd() function, to return a different date, click on insert, field object, formula field, and put your formula there.

Please post if you have any more questions.
Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[Roadkill150]

Lisa,
This seriously sounds like it is starting to make a mountain out of a molehill. If your orginal formula is indeed working correctly, and you just need to suppress the blank dates, there are a couple simple options. right click on the margin in the section you wish the blanks to be suppressed, select format section, select the formula box next to suppress create the formula:
length(totext({yourdateformulahere}))<2 and see if that does the job. if not, create the formula length(totext({yourdateformulahere})) insert this into your report next to the field showing the blank sections and see what the length reads.. then just set the number in the suppression formula 1 higher than the value you are seeing for your blank dates.
Cheers!
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com