A tableframe field named DailyValue has this for its calculation:
Number( [TBL.Sum of TotalCost] ) / Number( [TBL.EndDate] -[TBL.BgnDate]
)

When either the Bgn or End Date is blank DailyValue displays <Error>
I know what the problem is but I don’t know how to fix it.
Can someone help?

If either of those dates are blank, what would you expect it to display?

I guess I'd suggest an iif() in the calculation that took into account EITHER of
those fields being blank.



--

Tony McGuire
"It's not about having enough time. It's about priorities."
___________________________

Like Tony said, I would expect <Error> if either were blank.
Depending on the details, this might be OK. Question is how you want
it to work:

If EndDate is blank, do you want to use today in its place, or do you
want to return some other message (besides <Error>) if either date is
blank?

For example (spread across multiple lines for readability, but you
need it on one line for a calculated field):

iif (
isBlank([TBL.BgnDate]),
"Project hasn't started",
iif (
isBlank([TBL.EndDate]),
"Project isn't done",
iif (
[TBL.Sum of TotalCost]<.01,
"Total Cost Not Compiled",
Number( [TBL.Sum of TotalCost] ) /
Number( [TBL.EndDate] -[TBL.BgnDate] )
)

)

)

Liz


Jeanette wrote:

IN A PERFECT WORLD:
I would like the calculated field DailyValue to display $0.00 when either
of the date fields is blank

in the meantime I will play with your suggestion - some how I have in my
head that the culprit is an attempt to divide by zero...


Liz McGuire <liz (AT) paradoxcommunity (DOT) com> wrote:

Can you have end date without a begin date?


Probably.

Anyway, you can easily modify Liz example to get what you want.

Anders

Jeanette,

Just replace the "Project hasn't started", "Project isn't done" and
"Total Cost Not Compiled" with "0.00" and format the field using the
Currency format under Number.

I'm not sure what the error was, but FWIW, did this in a calculated
field:

number([Testd.endd]-[Testd.startd])

....(where endd was blank and startd was today's date) and got
-732,352.00 - which is what I was expecting (blank dates get converted
to 00 Dec 0000). Then I turned OFF "treat blank fields as zero" and
got a blank field (no error). I got a GPV when I tried to do this:

129875.99/number([Testd.endd]-[Testd.startd])

....with treat blank as zero off. (with it on, the above gives me a
negative number.)

P 11.0.0.410

Liz


Jeanette wrote: