Error# showing in Report calculated field because of no data

[Guest]

I have created a report which tracks grants received and how the money is
spent. Sometimes one receives a “gift in kind†instead of cash.

The Report is structured with a Foundation Header including the name of the
Donor Organization, an Application ID Header containing controls named:
DateSubmitted, Request, Outcome, OutcomeDate and a control with the formula
=IIf([AwardInKind] Is Not Null,"Award In Kind: " & [AwardInKind])

In the Detail section there is a Date field, a Description field and then
three controls with the following code:

Grant: =IIf([Amount]>0,[Amount])

Expense: =IIf([Amount]<0,[Amount])

Balance: =CCur([Amount])

When I have a Gift In Kind there is no amount information to feed into the
controls in the Detail section and so the Balance field shows up as Error#.

Is there any way that I can prevent the "Error#" from showing up?

Thank you for any light on the subject.

Mary

 

[Duane Hookom]

You can test for [HasData] like:
=IIf([HasData],[Amount],0)

 

[Marshall Barton]

I have created a report which tracks grants received and how the money is
spent. Sometimes one receives a “gift in kind” instead of cash.

The Report is structured with a Foundation Header including the name of the
Donor Organization, an Application ID Header containing controls named:
DateSubmitted, Request, Outcome, OutcomeDate and a control with the formula
=IIf([AwardInKind] Is Not Null,"Award In Kind: " & [AwardInKind])

In the Detail section there is a Date field, a Description field and then
three controls with the following code:

Grant: =IIf([Amount]>0,[Amount])

Expense: =IIf([Amount]<0,[Amount])

Balance: =CCur([Amount])

When I have a Gift In Kind there is no amount information to feed into the
controls in the Detail section and so the Balance field shows up as Error#.

Is there any way that I can prevent the "Error#" from showing up?

Probaly several ways to avoid the issue, but I would need to
know how the Amount control/field/whatever is
calculated/retrieved.

If you can not track that down and rectify whatever causes
#Error, then you can resort to using
=IIf(IsError(CCur([Amount])), Null, CCur([Amount]))

 

[Guest]

Hi Duane,

That worked like a charm in that it gave me a $0.00 balance. Thanks so much.
I suppose if I wanted nothing to be there I could put "" instead of 0 as the
Else result.

Mary

You can test for [HasData] like:
=IIf([HasData],[Amount],0)

--
Duane Hookom
MS Access MVP
--

I have created a report which tracks grants received and how the money is
spent. Sometimes one receives a "gift in kind" instead of cash.

The Report is structured with a Foundation Header including the name of
the
Donor Organization, an Application ID Header containing controls named:
DateSubmitted, Request, Outcome, OutcomeDate and a control with the
formula
=IIf([AwardInKind] Is Not Null,"Award In Kind: " & [AwardInKind])

In the Detail section there is a Date field, a Description field and then
three controls with the following code:

Grant: =IIf([Amount]>0,[Amount])

Expense: =IIf([Amount]<0,[Amount])

Balance: =CCur([Amount])

When I have a Gift In Kind there is no amount information to feed into the
controls in the Detail section and so the Balance field shows up as
Error#.

Is there any way that I can prevent the "Error#" from showing up?

Thank you for any light on the subject.

Mary

 

[Guest]

Hi Marshall,

In answer to your question, the Amount control data source is from an Amount
field in the underlying table.

I tried the code you offered, but I still get an Error# entry. I tried the
one that Duane offered above and that worked fine. Perhaps it is because
there is no calculation to do. In another context I'm sure it would work fine.

Thanks for your assistance.

Mary

I have created a report which tracks grants received and how the money is
spent. Sometimes one receives a “gift in kind†instead of cash.

The Report is structured with a Foundation Header including the name of the
Donor Organization, an Application ID Header containing controls named:
DateSubmitted, Request, Outcome, OutcomeDate and a control with the formula
=IIf([AwardInKind] Is Not Null,"Award In Kind: " & [AwardInKind])

In the Detail section there is a Date field, a Description field and then
three controls with the following code:

Grant: =IIf([Amount]>0,[Amount])

Expense: =IIf([Amount]<0,[Amount])

Balance: =CCur([Amount])

When I have a Gift In Kind there is no amount information to feed into the
controls in the Detail section and so the Balance field shows up as Error#.

Is there any way that I can prevent the "Error#" from showing up?

Probaly several ways to avoid the issue, but I would need to
know how the Amount control/field/whatever is
calculated/retrieved.

If you can not track that down and rectify whatever causes
#Error, then you can resort to using
=IIf(IsError(CCur([Amount])), Null, CCur([Amount]))