Error# showing in Report calculated field because of no data

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
sweeneysmsm said:
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?
Click to expand...


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]))
 
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

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

--
Duane Hookom
MS Access MVP
--

sweeneysmsm said:
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
Click to expand...
Click to expand...
 
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

Marshall Barton said:
sweeneysmsm said:
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?
Click to expand...


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]))
Click to expand...
 
Back
Top