Formula logic

[ShortyA]

I am having an odd problem with the formula below. When the numbervar is commented out the behaviour I want where if PRTY is 999 then PRTY works. However, if I put the numbervar line in then I just get a blank value. Does anyone have any thoughts as I am not sure why this is making such a difference ? The numbervar is used but I have commented it out for testing. Also this formula is calculated after the @Calc_Elap_Against_ASL formula.
Many thanks - ShortyA

Numbervar Diff := DateDiff("d",CurrentDate,if {@Calc_Elap_Against_ASL}>5 then 'g - Due in >5 days'
else 'Due in less than 5 days'
else {BI0001.PRTY}

 

[HowardHammerman]

the datediff function requires the string you supplied ("d&quot and then two date inputs. Your IF expression results in a string. Not good. I don't know why you got a blank value rather than a syntax error, but that is the problem. or at least part of it.

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[ShortyA]

Howard,
thanks for that. I amended and used the following but still getting a blank if there is no value for "Diff".

Numbervar Diff := DateDiff("d",CurrentDate,{BI0003.ACTDATTIM});
if Diff > 90 then "a - Due to wake in > 90 days"
else if Diff in 61 to 90 then "ab - Due to wake in 61-90 days"
else {BI0001.PRTY}

Which is tidier and should force PRTY to be the output (PRTY is a number). But it still is blank even when PRTY has a value.
ShortyA

 

[HowardHammerman]

Now the problem is in your IF statement. the first part is ok but the false part ouputs a number value and the true part outputs a string.

Again, I am surprised you did not get a syntax error.

Anyway, replace the last line with:

else totext({BI0001PRTY})



Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[ShortyA]

It is now amended to output a text value but it is still outputting blank values.

 

[HowardHammerman]

Either the value of B10001.PRTY is blank in some circumstances or Crystal just can't evaluate the expression.

I'm stumped.

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[ShortyA]

Me too. PRTY always has a value and as soon as I comment out the Numbervar definition it is fine. I have also tried commenting out parts of the if statements but no difference. It is a strange one but I have found out that if I put the datediff bit in the If statement eg if datediff)"d",CurrentDate,DateField) that it works!
Strange one.
ShortyA

 

[kskid]

I think you need to swap the dates for datediff

You have

datediff)"d",CurrentDate,DateField)

You need

datediff)"d",DateField,CurrentDate)

WichitaKid

 

[Reebo99]

Have you tested your fields for null values?

Reebo
UK