I have a form with four cost fields, which have a currency format:

Costs
Costs2
Costs3
Costs4

I can get them to add up using the following formula:

=Nz([Costs]+[Costs2]+[Costs3]+[Costs4])

My problem is that all 4 fields must have a currency value in order to add
up (so I have to put $0.00 in fields with no information). How to I get the
four fields to add up, if 1 to 3 of them do not have a value at all?

Any assistance would be greatly appreciated!

Brenda

You can protect against NULLs by strining together IIF statements

IIf(IsNull([Costs]),0,[Costs])+IIf(IsNull([Costs2]),0,[Costs2])+ ...

"B. Levien" wrote:

> I have a form with four cost fields, which have a currency format:
>
> Costs
> Costs2
> Costs3
> Costs4
>
> I can get them to add up using the following formula:
>
> =Nz([Costs]+[Costs2]+[Costs3]+[Costs4])
>
> My problem is that all 4 fields must have a currency value in order to add
> up (so I have to put $0.00 in fields with no information). How to I get the
> four fields to add up, if 1 to 3 of them do not have a value at all?
>
> Any assistance would be greatly appreciated!
>
> Brenda

On Thu, 9 Nov 2006 13:21:02 -0800, B. Levien
<(E-Mail Removed)> wrote:

>I have a form with four cost fields, which have a currency format:
>
>Costs
>Costs2
>Costs3
>Costs4

Then your table is incorrectly designed. If you have a one (whatever)
to many (costs) relationship, a much better design would use THREE
tables:

YourTable <don't know what it represents>
SomeField <primary key>

CostTypes
CostType <your Costs, Costs2 etc. > <primary key>

Costs
SomeField <link to your table>
CostType <link to CostTypes>
Amount <currency>

Thus if you ever need a FIFTH cost, you can simply add it rather than
redesigning your table, all your queries, all your forms, and all your
calculations.

>I can get them to add up using the following formula:
>
>=Nz([Costs]+[Costs2]+[Costs3]+[Costs4])
>
>My problem is that all 4 fields must have a currency value in order to add
>up (so I have to put $0.00 in fields with no information). How to I get the
>four fields to add up, if 1 to 3 of them do not have a value at all?

Wrap EACH of the costs in NZ. What you're doing here is getting a NULL
result - anything plus null is NULL - and *then* setting that to zero.

=NZ([Costs]) + NZ([Costs2]) + NZ([Costs3]) + NZ([Costs4])

will convert each NULL to a zero *first* - and since $12.50 + 0 =
$12.50, you'll get the right sum.

But... normalize your data, and use a Totals query; simply don't ENTER
any record if there is no cost for that category.

John W. Vinson[MVP]

John Vinson wrote:
> >I have a form with four cost fields, which have a currency format:
> >
> >Costs
> >Costs2
> >Costs3
> >Costs4
>
> Then your table is incorrectly designed. If you have a one (whatever)
> to many (costs) relationship, a much better design would use THREE
> tables:
>
> Thus if you ever need a FIFTH cost, you can simply add it rather than
> redesigning your table, all your queries, all your forms, and all your
> calculations.

But what if the business rule is there must always be four, no more no
less (including zero)? Having four columns makes such a constraint easy
to write: just make all columns NOT NULL (required = true/yes). With
your proposed design, such a constraint is harder to implement. Don't
you think there is a case for 'denormalize for data integrity'?

Jamie.

--

On 10 Nov 2006 01:58:22 -0800, "Jamie Collins"
<(E-Mail Removed)> wrote:

>ut what if the business rule is there must always be four, no more no
>less (including zero)? Having four columns makes such a constraint easy
>to write: just make all columns NOT NULL (required = true/yes). With
>your proposed design, such a constraint is harder to implement. Don't
>you think there is a case for 'denormalize for data integrity'?

<shrug> Could happen.

In my experience, whenever there are exactly four categories... some
manager will come along within a few months with a fifth. Case in
point: "The only thing worse than three bridge players trying to find
a fourth is four bridge players drinking a fifth." <g>

John W. Vinson[MVP]

Thank you all very much for your responses! They were very helpful &
informative!!!

I left all the costs fields in the table, rather than separating them into
separate tables, but generated a Costs Query with a field to add up the four
costs fields I needed (the number of costs fields will not change), using the
NZ value to include any field which did not have any information. I created
a report based upon that Query to show the total amount being charged.

"John Vinson" wrote:

> On 10 Nov 2006 01:58:22 -0800, "Jamie Collins"
> <(E-Mail Removed)> wrote:
>
> >ut what if the business rule is there must always be four, no more no
> >less (including zero)? Having four columns makes such a constraint easy
> >to write: just make all columns NOT NULL (required = true/yes). With
> >your proposed design, such a constraint is harder to implement. Don't
> >you think there is a case for 'denormalize for data integrity'?
>
> <shrug> Could happen.
>
> In my experience, whenever there are exactly four categories... some
> manager will come along within a few months with a fifth. Case in
> point: "The only thing worse than three bridge players trying to find
> a fourth is four bridge players drinking a fifth." <g>
>
> John W. Vinson[MVP]
>