n/a value

[diobbi]

I need to perform a calculation that takes 2 fields and divides them to come up with a number. I then need to format that number as a percent. If either of these fields is blank, I want to put "N/A" on the report. Here is the formula:
if IsNull({StubResultsSub.EBIT}) then "n/a" else
({StubResultsSub.EBIT}/{StubResultsSub.LATESTSALES})*100

The problem is it won't let me do this. I keep getting:
A string is required after the else. I could convert the formula to a string using cstr however then I can't format the result to display the percent sign. Does anyone know how to do this? Thanks.

Deborah

 

[Naith]

Your problem is that you're telling the field to return sometimes a string value, sometimes a numerical value; which it isn't really keen on doing.

Both possible outputs have to be the same datatype. So...you might want to do something like

If IsNull({StubResultsSub.EBIT})
Then "N/A"
Else
ToText(({StubResultsSub.EBIT}%{StubResultsSub.LATESTSALES}),0)

The thing is, you'll have to convert the field back to a number.

Another solution which gets you around the need for conversion could be accomplished with you changing the &quot;N/A&quot; part of the formula to -999999, and overlay an additional formula on top of this field on the report. This new formula would just say &quot;N/A&quot;, but would be conditionally suppressed on your original formula <> -999999. Your original formula would be conditionally suppressed on -999999. So, although both fields are sitting on each other on the report, only one will show up at a time.

Naith

 

[basil3legs]

You could do something like :

if (IsNull({StubResultsSub.EBIT}) or {StubResultsSub.EBIT} = 0) then &quot;n/a&quot; else
CStr(({StubResultsSub.EBIT}/{StubResultsSub.LATESTSALES})*100,2)+&quot;%&quot;

This returns n/a if EBIT is null or 0 (to avoid Divide by Zero errors) and returns the percentage as a string to 2 decimal places (you can change this as required) with the % attached. I seem to remember the isnull has to be first or it won't work, Crystal is funny that way!

 

[diobbi]

Thanks for the help. I tried basil3legs's suggestion and it worked like a charm. I appreciate the fast response.

Deb