Combining formulas

[mark12010]

I have 2 formulas in a sub-report.One gives me the number of days since a patients last appt. and one displays "NO APPT. DATA ON FILE" if {billing_tx_history.date_of_service} is Null. How can I combine the 2? The formulas are as follows
1) Numbervar Days;
Days := DateDiff ("d",{billing_tx_history.date_of_service}, CurrentDate);

2) IF ISNULL ({billing_tx_history.date_of_service})
THEN "NO APPT DATA ON FILE"

I am running this on CR XI

 

[mark12010]

I got this from -LB
Numbervar Days;
if (
isnull({billing_tx_history.date_of_service}) or
TRIM({billing_tx_history.date_of_service})=""
) then
Days := 99999999 else
Days := DateDiff ("d",{billing_tx_history.date_of_service}, CurrentDate);
if Days = 99999999 then
"No appt data on file" else
"Patient last seen " + totext(Days,0,"") + " days ago";

but it gives me an error at:
TRIM({billing_tx_history.date_of_service})=""
{billing_tx_history.date_of_service} is highlighted and it says
A sting is required here.

 

[IanWaterman]

Just remove or TRIM({billing_tx_history.date_of_service})=""

This was added as I believe the original formula was developed for the field {billing_tx_history.date_of_service} being a datestring. As your field is a date isnull() is sufficient.

Ian

 

[mark12010]

I removed or TRIM({billing_tx_history.date_of_service})=""

Numbervar Days;
if isnull({billing_tx_history.date_of_service})
then Days := 99999999
else Days := DateDiff ("d",{billing_tx_history.date_of_service},CurrentDate);
if Days = 99999999 then
"No appt data on file" else
"Patient last seen " + totext(Days,0,"") + " days ago";
It did not throw any errors and it works if ({billing_tx_history.date_of_service}) has a value but if it isnull it just shows a blank field

 

[IanWaterman]

In File ->Report Options is the Convert database nulls to default checked. If it is try unchecking.

Ian

 

[mark12010]

It is not checked. This shoudnt be this hard I dont know why it isnt working.

 

[lbass]

Try:

Numbervar Days;
if isnull({billing_tx_history.date_of_service}) or
{billing_tx_history.date_of_service} = date(0,0,0) then
Days := 99999999 else
Days := DateDiff ("d",{billing_tx_history.date_of_service},CurrentDate);
if Days = 99999999 then
"No appt data on file" else
"Patient last seen " + totext(Days,0,"") + " days ago";

-LB

 

[mark12010]

-LB I tried what you said and It still only works if ({billing_tx_history.date_of_service}) has a value but if it isnull it just shows a blank field

 

[lbass]

Please explain where you are placing this formula (what section).

Please do a test and place this formula in the detail section:

if isnull({billing_tx_history.date_of_service}) or
{billing_tx_history.date_of_service} = date(0,0,0) then
99999999

And then also add a formula like this:

len(totext({billing_tx_history.date_of_service},"MM/dd/yyyy"))

Then report back on the results for these formulas.

-LB

 

[mark12010]

when I use:
if isnull({billing_tx_history.date_of_service}) or
{billing_tx_history.date_of_service} = date(0,0,0) then
99999999
it returns 99999999 if billing_tx_history.date_of_service isnull and returns 0 if billing_tx_history.date_of_service is not null

when I run:
len(totext({billing_tx_history.date_of_service},"MM/dd/yyyy"))
it shows a blank field if billing_tx_history.date_of_service isnull and returns 10 if billing_tx_history.date_of_service is not null
I dont see how this is going to get me what i need.

 

[lbass]

Well, that tells me there is nothing wrong with the formula, so I'm wondering whether you have added a reset formula somewhere that sets Days := 0?

Otherwise I think you should copy and paste your actual formula into the thread (if you didn't earlier). Specifically, I wonder whether you have the same number of 9's in both places or whether you have incorrect punctuation in the formula.

-LB