URGENT HELP PLS: running sum/<=100%

[ella]

hi!

i have defined nine fields which may or may not be filled out (e.g. only five fields could be filled out, the rest of the fields will just be blank). i've created a new field on my query to add the nine fields. however, it's only working when all nine fields have been filled out. here's the formula:

Percentage Sum:
([Letter of Wishes]![Percentage 1]+[Letter of Wishes]![Percentage 2]+[Letter of Wishes]![Percentage 3]+[Letter of Wishes]![Percentage 4]+[Letter of Wishes]![Percentage 5]+[Letter of Wishes]![Percentage 6]+[Letter of Wishes]![Percentage 7]+[Letter of Wishes]![Percentage 8]+[Letter of Wishes]![Percentage 9]

questions:

1. how do i create a running sum formula for these nine fields?
2. what condition (if...then...else???)will i make so that if the running sum is already 100%, the system will prompt the user that he/she is already exceeding 100%

thanks very much! i'd really appreciate your help.

 

[Jack Cowley]

I assume that the fields have a number Data type so you need the Nz() function to change Null fields, which you can't add, to 0 so you can add them. Your code would look like this:

Nz([Letter of Wishes]![Percentage 1])+ Nz([Letter of Wishes]![Percentage 2])+ Nz([...

In your form you will have to check in the Before Update event of all 9 fields, using code similar to the code above, to check the total of the fields and if it exceeds 100% then let the user know...