Field Addition Complication

[RaiTidR]

Hello again...

I got almost everything working on the form except for the Grand Total feature.

I tried the

=[field1]+[field2]+[field3]+[field4]+[field5]+[field6]+[field7]

and so on and so forth to the point it would add all the fields up into a grand total.

The only problem I run into is it won't add them up unless there is at least a value of $0.00 in the field. Which makes it look VERY cluttered since on average only 1/3 of the forum will be used regularly.

Is there any way to make it show the grand total even if a field is blank?

 

[ansentry]

Put this in the query that is the data source for your form (I have only done up to Four)

TOTAL: NZ([ONE])+NZ([TWO])+NZ([THREE])+NZ([FOUR])

or in an unbound control on the form:

=NZ([One])+NZ([Two])+NZ([Three])+NZ([Four])

Putting it in the query means that the updating is quicker.... my experience.

 

[RaiTidR]

I don't understand?

---

I initially made the form as only a Customer Contract database. A Simple data entry type of document that would allow us to have a digital file of existing contracts.

However, my boss was so impressed with how clean the design was that he wanted me to explore the possibility of allowing it to be put on laptops for the salesman to actually estimate with the digital contract.

So there is little doubt in my mind that the form in most aspects is very inefficient.

 

[ansentry]

Pat,

I have attached a sample db; I would be pleased if you could point out what I have done wrong.

How would this be done in my example:

Of course normalizing your data would be an option so you don't have to manually sum a bunch of columns.

In the query that is the source for my subform I used the +, how could I have avoided using them.

 

[RaiTidR]

I'm slightly confused as to what field these codes would go in.

TOTAL: NZ([ONE])+NZ([TWO])+NZ([THREE])+NZ([FOUR])

Should it be like the following

TOTAL: NZ([FIELDVALUE01])+NZ([FIELDVALUE02])

Am I understanding this correctly.

---

Edit: Ok I tried this... don't know if this was right. It still calculates but only if the field value has at least a 0.

=NZ([TEAROFF1_PERSQ])+NZ([TEAROFF2_PERSQ])+NZ([INSTALL1_PERSQ])+NZ([INSTALL2_PERSQ])+NZ([INSTALL3_PERFT])+NZ([INSTALL4_PERFT])+NZ([INSTALL5_PERSQ])+NZ([INSTALL6_EACH])+NZ([INSTALL7_EACH])+NZ([INSTALL8_EACH])+NZ([DEBRIS_RMV_PERSQ])+NZ([REFLASH_CHM_PERFT])+NZ([WALLSTEP_FLSH_PERFT])+v[VALLEY_FLSH_PERFT])+NZ([ICEANDWATER_PERFT])+NZ([STEEP_PERSQ])+NZ([HEIGHT_PERSQ])+NZ([MANSARD_PERSQ])+NZ([UPGRADE_SHNG_PERSQ])+NZ([INSTALL_GUTTERS_PERFT])+NZ([INSTALL_DWNSPT_PERFT])+NZ([INSTALL_SIDE_PERSQ])+NZ([INSTALL_INSUL_PERSQ])+NZ([FACIA_PERFT])+NZ([WINDOW_WRAP_EACH])+NZ([MISC1_PER])+NZ([MISC2_PER])

 

[ansentry]

Have a look at the sample db I posted.

 

[RaiTidR]

I did... and by every standard of math i know it doesn't seem to calculate anything.

I looked in design view of the form... but since I couldn't tell what was supposed to be calculated and what wasn't I really couldn't decipher it.

Furthermore, I don't know if it was intentional but on my computer. THe grand total box was empty regardless of what I did with the boxes at the top.

 

[ansentry]

Just so I am clear on what is happening.

When you open my database are the calculations on the form working?

Have you looked at the datasource for the subform?

Have you looked at the unbound controls that do the calculation in the subform (Open it in design view and expland it so that you can see the unbound controls)

Look at how TTL in the subform get its data.

 

[ansentry]

This is what the main form in my db should look like: