asked on

A Number is required here - error in Formula

Hi All,
Sorry about the basic question but I am new to Crystal

I have a lot of Null entries and I wish to display a string where a entry is null. I am tryiang the Formula below but it keeps coming back with the error message " a number is required here"

stringVar Message := "Patient to seen or time seen not recorded";

If not Isnull({@Seen Date-Time})
then
(
DateDiff ("n",{@Referral Date-Time} ,{@Seen Date-Time} )
)
else
Message;

Does anyone have any idea I might do this??

Thanks a million,
Nicky

GJParker

Your problem here is that crystal functions will only allow you to return one data type.

In your formula if the field is null then the calculation returns a number where as if the field is null you are attempting to return a string.

One way around this would be to convert the Datediff calculation to a string i.e.

stringVar Message := "Patient to seen or time seen not recorded";

If not Isnull({@Seen Date-Time})
then
(
ToText(DateDiff ("n",{@Referral Date-Time} ,{@Seen Date-Time} ))
)
else
Message;

Gary

NickyNin

ASKER

Thanks for your reply!!

Unfortunately I need the result to be a number as I then use this number in a calculation. It tells me that the "a number is required here"

I have tried to convert it back into a number ( see Below) but I get the error message " The string is non numeric"

if IsNull ({@DateDiff})
then 0
else if ToNumber({@DateDiff}) < 10
then -6
else if ToNumber({@DateDiff}) in 10 to 60
then -5
else if ToNumber({@DateDiff}) in 60 to 120
then -4
else if ToNumber({@DateDiff}) in 120 to 240
then -3
else if ToNumber({@DateDiff}) > 240
then -2
else 0

ASKER CERTIFIED SOLUTION

GJParker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial