Adding Fields in an Access 2003 Form, including null fields

[Guest]

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

 

[Guest]

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]

 

[Jamie Collins]

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.

--

 

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

 

[Guest]

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.