Hi. I have a calculation field, and when data is not available, it returns a question mark. How do I tell FMP to leave the field blank, when there is insufficient data to make that calc?

I tried turning it into a number field, and lookup the calc. That allows me to manually delete the question mark out of the field, but that doesn't seem like the best solution to me.

Any help is appreciate.

Thanks!

-Emery

What does your calculation field look like? If you are using a Case statement with a "" result for a non-text field, then the null ("") must either be converted via TextToDate, TextToTime, etc. to the proper type or a default result should not be used, e.g. Case( thatfield = "this", Status(CurrentDate) ), so the calculation will not evaluate a result if the test is not true.

The question mark usually means the calculation result is undefined, very often a divide by zero (or, that the number of digits returned doesn't fit in the field displayed)

Test the values used in the calculation, with an IF statement, e.g.

if (denominator, numerator/denominator, "")

Or use a Case statement without the unnecessary null result.

Thanks, everyone. Below is the calculation. Where I have (no data) is where data does not exist. This is a rate calculation, and looks at client accounts over time. Data will be returned if the account is old enough to contain data, but not if the account isn't old enough. It is for the accounts that aren't old enough, that I get the question marks in the calc field.

So what I need is some way to tell the calc field to just return nothing, rather than a question mark, if no data is available. Thanks.

((no data) /(no data)) ^ (1/10)-1

Hi, Emery. Following on Gregory's & JT's suggestions, try this calc:

Case ( Not IsEmpty ( FieldName ) and FieldName <> 0 , ( FieldName / FieldName ) ^ ( 1 / 10 ) - 1 )

HTH,

Jerry

Thanks, Jerry and everyone. I actually used a modified version of this, with an "If" function, and it works. But you guys put me on the right track.

Thanks again.

-Emery