Forms Calculations

[Guest]

Hi, I am creating a Form for Returns. It consists of 9 lines of detail that
include a fields (defined in my table) for Quantity and Unit Price (9 each).
On the form I also have 9 Sub-total fields with the Control Source defined as
=[QTY]*[UNIT]. (These fields are NOT defined in my table.)

These 9 lines work fine and the subtotal field (called TOTAL) is filled as
each line is entered.

My last field, which is defined in the table as I want to keep a record of
it is Grand Total. It is the Grand Total that does not work. My Control
Source calculation for Grand Total:

=[TOTAL1]+[TOTAL2]+[TOTAL3]+[TOTAL4]+[TOTAL5]+[TOTAL6]+[TOTAL7]+[TOTAL8]+[TOTAL9]

If I reduce the fields to the ones that have been filled out on the form
(say 4 lines of detail are filled and the calc only includes those 4 lines)
it will work but will all 9 it will only work if all 9 lines are filled even
though all fields indicated above (UNIT, QTY and TOTAL) default to a zero
value.

I know I'm missing something small. I've been away from Access for too
long. HELP!!

 

[John Vinson]

Hi, I am creating a Form for Returns. It consists of 9 lines of detail that
include a fields (defined in my table) for Quantity and Unit Price (9 each).

If your table has nine Quantity fields and nine Unit Price fields - IT
IS INCORRECTLY DESIGNED.

"Fields are expensive, records are cheap". Take a look at the
Northwind sample database for a normalized way to do this.

On the form I also have 9 Sub-total fields with the Control Source defined as
=[QTY]*[UNIT]. (These fields are NOT defined in my table.)

These 9 lines work fine and the subtotal field (called TOTAL) is filled as
each line is entered.

My last field, which is defined in the table as I want to keep a record of
it is Grand Total. It is the Grand Total that does not work. My Control
Source calculation for Grand Total:

=[TOTAL1]+[TOTAL2]+[TOTAL3]+[TOTAL4]+[TOTAL5]+[TOTAL6]+[TOTAL7]+[TOTAL8]+[TOTAL9]

If I reduce the fields to the ones that have been filled out on the form
(say 4 lines of detail are filled and the calc only includes those 4 lines)
it will work but will all 9 it will only work if all 9 lines are filled even
though all fields indicated above (UNIT, QTY and TOTAL) default to a zero
value.

I know I'm missing something small. I've been away from Access for too
long. HELP!!

If you include any NULL value in an arithmetic expression, the entire
result will be NULL. To directly fix this problem use the NZ()
function to convert NULL to zero:

=NZ([TOTAL1])+NZ([TOTAL2])+... <etc>

But MUCH better would be to scrap this spreadsheet and use a properly
normalized Order - OrderDetails table structure instead.

John W. Vinson[MVP]