Adding Fields in an Access 2003 Form, including null fields

[B. Levien]

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

 

[Bruce Meneghin]

You can protect against NULLs by strining together IIF statements

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

 

[John Vinson]

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]

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]

 

[B. Levien]

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.