Formula That Works in Footer Bombs in Detail

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BarryC - 08 Dec 2005 15:39 GMT

I have a formula that works fine in the report footer (with the
addition of the Sum Fx where appropriate) but bombs by showing #Error
when it is in the detail of the report.

The formula is: =IIf([Minutes]\60>=1,[Minutes]\60 & " Hr " & [Minutes]
Mod 60 & " Min",[Minutes] Mod 60 & " Min")

[Minutes] is an integer.

When it is placed in the report footer as:
=IIf(Sum([Minutes])\60>=1,Sum([Minutes])\60 & " Hr " & Sum([Minutes])
Mod 60 & " Min",Sum([Minutes]) Mod 60 & " Min")

it works fine.

Reply to this Message

Ken Snell (MVP) - 08 Dec 2005 16:08 GMT

Any chance that the value of [Minutes] could be Null in any of the report's
records? Null will cause an error when you try to use it in the first
expression's calculations, whereas Sum function ignores Null values.

Signature

Ken Snell
<MS ACCESS MVP>

>I have a formula that works fine in the report footer (with the
> addition of the Sum Fx where appropriate) but bombs by showing #Error
[quoted text clipped - 10 lines]
>
> it works fine.

Reply to this Message

BarryC - 08 Dec 2005 18:10 GMT

Most of the records do have a number in them, there was at least one
that I know was null. All of the lines show #Error however.

I put a NZ function in the formula to trap for Null; still every line
is #Error.

Reply to this Message

BarryC - 08 Dec 2005 18:33 GMT

Ken,
It was that old "Textbox named the same as the Datafield" bug again.
After I renamed the textbox (and fixed the formula where it
automatically substituted the box name for the datafield - WTF?) it
worked OK.

Thanks!

Reply to this Message

Ken Snell (MVP) - 08 Dec 2005 20:43 GMT

Reports are more prone to that renaming issue in expressions when you change
the name of a textbox from the name of its bound field.
Glad it's working now.

Signature

Ken Snell
<MS ACCESS MVP>

> Ken,
> It was that old "Textbox named the same as the Datafield" bug again.
[quoted text clipped - 3 lines]
>
> Thanks!

Reply to this Message