Sum of multiple boxes

[Steve Stad]

I have four boxes on a form which I sum in a fifth box using
=(Nz([text1],0))+(Nz([text2],0))+(Nz([text3],0))+(Nz([text4],0)).

I would like to control (e.g., stop and message) if on the input of any of
the first four boxes if the sum of the four boxes is > 100%. All of the
fields are formatted as a percent. Any code, technique, method or tip is
greatly appreciated. This example could represent contributions to four
mutual funds in a 401K plan where you can NOT contribute more than 100%.

 

[Allen Browne]

Open the table that this form saves its data to.
In table design, open the Properties box.
Beside the Validation Rule in the Properties box, enter this (as one line):
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1]) <
1.00000001

(Be sure to use the Validation Rule in the Properties box, not the one in
the lower pane of table design, which is the rule for one field.)

Set the validation Text to a suitable message, such as:
The four boxes must not exceed 100%.

I've assumed the 4 fields are of type Number, size Double. Floating point
values may be slightly higher than 100%, so we have to give them a little
margin to cope with those inaccuracies.

Now you won't be able to save the value that exceed 100% in your form. You
could also use Conditional Formatting on the 5th text box so it turns red if
it exceeds 100%. This is less intrusive than message boxes popping up, but
it still visually notifies the user that the record can't be saved in its
current state.

 

[Steve Stad]

Allen,

I have been testing the code. It seems to work sometimes but not always.
and when I change the values to something below 100% I still get message .
Can you explain logic of formula....it looks like it is adding text1 value
four times and you use "<" less than one ??
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1]) <
1.00000001

Open the table that this form saves its data to.
In table design, open the Properties box.
Beside the Validation Rule in the Properties box, enter this (as one line):
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1]) <
1.00000001

(Be sure to use the Validation Rule in the Properties box, not the one in
the lower pane of table design, which is the rule for one field.)

Set the validation Text to a suitable message, such as:
The four boxes must not exceed 100%.

I've assumed the 4 fields are of type Number, size Double. Floating point
values may be slightly higher than 100%, so we have to give them a little
margin to cope with those inaccuracies.

Now you won't be able to save the value that exceed 100% in your form. You
could also use Conditional Formatting on the 5th text box so it turns red if
it exceeds 100%. This is less intrusive than message boxes popping up, but
it still visually notifies the user that the record can't be saved in its
current state.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have four boxes on a form which I sum in a fifth box using
=(Nz([text1],0))+(Nz([text2],0))+(Nz([text3],0))+(Nz([text4],0)).

I would like to control (e.g., stop and message) if on the input of any of
the first four boxes if the sum of the four boxes is > 100%. All of the
fields are formatted as a percent. Any code, technique, method or tip is
greatly appreciated. This example could represent contributions to four
mutual funds in a 401K plan where you can NOT contribute more than 100%.

.

 

[John W. Vinson]

Allen,

I have been testing the code. It seems to work sometimes but not always.
and when I change the values to something below 100% I still get message .
Can you explain logic of formula....it looks like it is adding text1 value
four times and you use "<" less than one ??
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1]) <
1.00000001

I think Allen was just copying and pasting and got in a hurry, and assumed
that you would understand the code and correct the copy/paste duplicates: each
IIF should refer to its own textbox, e.g. Text1 with Text1, Text2 with Text2
and so on.

 

[Allen Browne]

Sorry: didn't change all the copy'n'pastes:

IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text2]) +
IIf([text3] Is Null, 0, [text3]) + IIf([text4] Is Null, 0, [text4]) <
1.00000001


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have been testing the code. It seems to work sometimes but not always.
and when I change the values to something below 100% I still get message .
Can you explain logic of formula....it looks like it is adding text1 value
four times and you use "<" less than one ??
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1]) <
1.00000001

Open the table that this form saves its data to.
In table design, open the Properties box.
Beside the Validation Rule in the Properties box, enter this (as one
line):
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1]) <
1.00000001

(Be sure to use the Validation Rule in the Properties box, not the one in
the lower pane of table design, which is the rule for one field.)

Set the validation Text to a suitable message, such as:
The four boxes must not exceed 100%.

I've assumed the 4 fields are of type Number, size Double. Floating point
values may be slightly higher than 100%, so we have to give them a little
margin to cope with those inaccuracies.

Now you won't be able to save the value that exceed 100% in your form.
You
could also use Conditional Formatting on the 5th text box so it turns red
if
it exceeds 100%. This is less intrusive than message boxes popping up,
but
it still visually notifies the user that the record can't be saved in its
current state.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have four boxes on a form which I sum in a fifth box using
=(Nz([text1],0))+(Nz([text2],0))+(Nz([text3],0))+(Nz([text4],0)).

I would like to control (e.g., stop and message) if on the input of any
of
the first four boxes if the sum of the four boxes is > 100%. All of
the
fields are formatted as a percent. Any code, technique, method or tip
is
greatly appreciated. This example could represent contributions to
four
mutual funds in a 401K plan where you can NOT contribute more than
100%.

.

 

[Steve Stad]

Thanks - The logic and message works fine in the form - which is good. The
message appears when you get to the end of the form or move to next record.
How can I trigger the message as soon as the field is filled in that puts the
total over 1 ( or 100%).

The table mode seems to function differently but I will be in form mode
mostly.

Sorry: didn't change all the copy'n'pastes:

IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text2]) +
IIf([text3] Is Null, 0, [text3]) + IIf([text4] Is Null, 0, [text4]) <
1.00000001


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have been testing the code. It seems to work sometimes but not always.
and when I change the values to something below 100% I still get message .
Can you explain logic of formula....it looks like it is adding text1 value
four times and you use "<" less than one ??
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1]) <
1.00000001

Open the table that this form saves its data to.
In table design, open the Properties box.
Beside the Validation Rule in the Properties box, enter this (as one
line):
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1]) <
1.00000001

(Be sure to use the Validation Rule in the Properties box, not the one in
the lower pane of table design, which is the rule for one field.)

Set the validation Text to a suitable message, such as:
The four boxes must not exceed 100%.

I've assumed the 4 fields are of type Number, size Double. Floating point
values may be slightly higher than 100%, so we have to give them a little
margin to cope with those inaccuracies.

Now you won't be able to save the value that exceed 100% in your form.
You
could also use Conditional Formatting on the 5th text box so it turns red
if
it exceeds 100%. This is less intrusive than message boxes popping up,
but
it still visually notifies the user that the record can't be saved in its
current state.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have four boxes on a form which I sum in a fifth box using
=(Nz([text1],0))+(Nz([text2],0))+(Nz([text3],0))+(Nz([text4],0)).

I would like to control (e.g., stop and message) if on the input of any
of
the first four boxes if the sum of the four boxes is > 100%. All of
the
fields are formatted as a percent. Any code, technique, method or tip
is
greatly appreciated. This example could represent contributions to
four
mutual funds in a 401K plan where you can NOT contribute more than
100%.

.

.

 

[Allen Browne]

You could write code in the AfterUpdate event of all 4 controls on the form,
that sums them and gives a MsgBox if it's > 1.0000001

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks - The logic and message works fine in the form - which is good.
The
message appears when you get to the end of the form or move to next
record.
How can I trigger the message as soon as the field is filled in that puts
the
total over 1 ( or 100%).

The table mode seems to function differently but I will be in form mode
mostly.

Sorry: didn't change all the copy'n'pastes:

IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text2]) +
IIf([text3] Is Null, 0, [text3]) + IIf([text4] Is Null, 0, [text4]) <
1.00000001


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have been testing the code. It seems to work sometimes but not
always.
and when I change the values to something below 100% I still get
message .
Can you explain logic of formula....it looks like it is adding text1
value
four times and you use "<" less than one ??
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1])
+
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1])
<
1.00000001

:

Open the table that this form saves its data to.
In table design, open the Properties box.
Beside the Validation Rule in the Properties box, enter this (as one
line):
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0,
[text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0,
[text1]) <
1.00000001

(Be sure to use the Validation Rule in the Properties box, not the one
in
the lower pane of table design, which is the rule for one field.)

Set the validation Text to a suitable message, such as:
The four boxes must not exceed 100%.

I've assumed the 4 fields are of type Number, size Double. Floating
point
values may be slightly higher than 100%, so we have to give them a
little
margin to cope with those inaccuracies.

Now you won't be able to save the value that exceed 100% in your form.
You
could also use Conditional Formatting on the 5th text box so it turns
red
if
it exceeds 100%. This is less intrusive than message boxes popping up,
but
it still visually notifies the user that the record can't be saved in
its
current state.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have four boxes on a form which I sum in a fifth box using
=(Nz([text1],0))+(Nz([text2],0))+(Nz([text3],0))+(Nz([text4],0)).

I would like to control (e.g., stop and message) if on the input of
any
of
the first four boxes if the sum of the four boxes is > 100%. All of
the
fields are formatted as a percent. Any code, technique, method or
tip
is
greatly appreciated. This example could represent contributions to
four
mutual funds in a 401K plan where you can NOT contribute more than
100%.

.

.

 

[Steve Stad]

Allen - this code works for a message box if sum of allocation is > 100% -
but how can I stop/halt input and force user to change alloc numbers to equal
less than 100%.

Private Sub CDM_Alloc_AfterUpdate()
If (Nz([PSR_Alloc], 0)) + (Nz([CCQAS_Alloc], 0)) + (Nz([CDM_Alloc], 0)) +
(Nz([NMIS_Alloc], 0)) + (Nz([TOL_Alloc], 0)) _
+ (Nz([EWSR_Alloc], 0)) > 1.000001 Then MsgBox "Allocation Can Not be
greater than 100%"
End Sub

ps... I changed text1,2,3,4 to six new fieldnames.

You could write code in the AfterUpdate event of all 4 controls on the form,
that sums them and gives a MsgBox if it's > 1.0000001

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks - The logic and message works fine in the form - which is good.
The
message appears when you get to the end of the form or move to next
record.
How can I trigger the message as soon as the field is filled in that puts
the
total over 1 ( or 100%).

The table mode seems to function differently but I will be in form mode
mostly.

Sorry: didn't change all the copy'n'pastes:

IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text2]) +
IIf([text3] Is Null, 0, [text3]) + IIf([text4] Is Null, 0, [text4]) <
1.00000001


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Allen,

I have been testing the code. It seems to work sometimes but not
always.
and when I change the values to something below 100% I still get
message .
Can you explain logic of formula....it looks like it is adding text1
value
four times and you use "<" less than one ??
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0, [text1])
+
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0, [text1])
<
1.00000001

:

Open the table that this form saves its data to.
In table design, open the Properties box.
Beside the Validation Rule in the Properties box, enter this (as one
line):
IIf([text1] Is Null, 0, [text1]) + IIf([text2] Is Null, 0,
[text1]) +
IIf([text3] Is Null, 0, [text1]) + IIf([text4] Is Null, 0,
[text1]) <
1.00000001

(Be sure to use the Validation Rule in the Properties box, not the one
in
the lower pane of table design, which is the rule for one field.)

Set the validation Text to a suitable message, such as:
The four boxes must not exceed 100%.

I've assumed the 4 fields are of type Number, size Double. Floating
point
values may be slightly higher than 100%, so we have to give them a
little
margin to cope with those inaccuracies.

Now you won't be able to save the value that exceed 100% in your form.
You
could also use Conditional Formatting on the 5th text box so it turns
red
if
it exceeds 100%. This is less intrusive than message boxes popping up,
but
it still visually notifies the user that the record can't be saved in
its
current state.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have four boxes on a form which I sum in a fifth box using
=(Nz([text1],0))+(Nz([text2],0))+(Nz([text3],0))+(Nz([text4],0)).

I would like to control (e.g., stop and message) if on the input of
any
of
the first four boxes if the sum of the four boxes is > 100%. All of
the
fields are formatted as a percent. Any code, technique, method or
tip
is
greatly appreciated. This example could represent contributions to
four
mutual funds in a 401K plan where you can NOT contribute more than
100%.

.

.

.

 

[John W. Vinson]

Allen - this code works for a message box if sum of allocation is > 100% -
but how can I stop/halt input and force user to change alloc numbers to equal
less than 100%.

Private Sub CDM_Alloc_AfterUpdate()
If (Nz([PSR_Alloc], 0)) + (Nz([CCQAS_Alloc], 0)) + (Nz([CDM_Alloc], 0)) +
(Nz([NMIS_Alloc], 0)) + (Nz([TOL_Alloc], 0)) _
+ (Nz([EWSR_Alloc], 0)) > 1.000001 Then MsgBox "Allocation Can Not be
greater than 100%"
End Sub

Use the BeforeUpdate event instead of the AfterUpdate event, and set its
Cancel argument to True. The user won't be able to leave the field if the
total exceeds 1.

 

[Steve Stad]

John,

Can you provide a little more detail regarding 'cancel = true'. I tried
this code but now the cursor will not move off of the PSR_Alloc field at all.
Sorry I am not a programmer so I need a little more detail.

Private Sub PSR_Alloc_BeforeUpdate(Cancel As Integer)
If (Nz([PSR_Alloc], 0)) + (Nz([CCQAS_Alloc], 0)) + (Nz([CDM_Alloc], 0)) +
(Nz([NMIS_Alloc], 0)) + (Nz([TOL_Alloc], 0)) _
+ (Nz([EWSR_Alloc], 0)) > 1.000001 Then MsgBox "Allocation Can Not be
greater than 100%"
Cancel = True
End Sub

Allen - this code works for a message box if sum of allocation is > 100% -
but how can I stop/halt input and force user to change alloc numbers to equal
less than 100%.

Private Sub CDM_Alloc_AfterUpdate()
If (Nz([PSR_Alloc], 0)) + (Nz([CCQAS_Alloc], 0)) + (Nz([CDM_Alloc], 0)) +
(Nz([NMIS_Alloc], 0)) + (Nz([TOL_Alloc], 0)) _
+ (Nz([EWSR_Alloc], 0)) > 1.000001 Then MsgBox "Allocation Can Not be
greater than 100%"
End Sub

Use the BeforeUpdate event instead of the AfterUpdate event, and set its
Cancel argument to True. The user won't be able to leave the field if the
total exceeds 1.

 

[John W. Vinson]

Can you provide a little more detail regarding 'cancel = true'. I tried
this code but now the cursor will not move off of the PSR_Alloc field at all.
Sorry I am not a programmer so I need a little more detail.

Use an if block rather than an inline IF, so that Cancel is only set to true
when there's a problem:

Private Sub PSR_Alloc_BeforeUpdate(Cancel As Integer)
If (Nz([PSR_Alloc], 0)) + (Nz([CCQAS_Alloc], 0)) + (Nz([CDM_Alloc], 0)) _
+ (Nz([NMIS_Alloc], 0)) + (Nz([TOL_Alloc], 0)) _
+ (Nz([EWSR_Alloc], 0)) > 1.000001 Then
MsgBox "Allocation Can Not be greater than 100%"
Cancel = True
End If
End Sub

 

[Allen Browne]

Steve, I think this approach is unproductive and frustrating.

The main thing is that the user can't save the *record* if the sum is more
than 100%. Fair enough, and the Validation Rule prevents that.

Now you're trying to not let the user out of the box until they get the
value right, but the problem may be in one of the other boxes, so they need
to get out of the box to fix it. For example, say the enters this:
Text0: 50% (should have been 5%)
Text1: 25%
Text2: 25%
Text4: 45%
When they enter the 45% in the final box, if you cancel the BeforeUpdate
event of that box, they now can't get out to go back and fix the Text0. They
have to figure out that they must set Text4 to zero, then go back to Text0
and change it to 5%, and then go back to Text4 to enter the right amount.

It would be a much less frustrating interface if you just had a calculated
total that went red to indicate a problem, and the validation rule that
prevents the record being saved, without this code to prevent them getting
to the box they need to correct.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

John,

Can you provide a little more detail regarding 'cancel = true'. I tried
this code but now the cursor will not move off of the PSR_Alloc field at
all.
Sorry I am not a programmer so I need a little more detail.

Private Sub PSR_Alloc_BeforeUpdate(Cancel As Integer)
If (Nz([PSR_Alloc], 0)) + (Nz([CCQAS_Alloc], 0)) + (Nz([CDM_Alloc], 0)) +
(Nz([NMIS_Alloc], 0)) + (Nz([TOL_Alloc], 0)) _
+ (Nz([EWSR_Alloc], 0)) > 1.000001 Then MsgBox "Allocation Can Not be
greater than 100%"
Cancel = True
End Sub

Allen - this code works for a message box if sum of allocation is >
100% -
but how can I stop/halt input and force user to change alloc numbers to
equal
less than 100%.

Private Sub CDM_Alloc_AfterUpdate()
If (Nz([PSR_Alloc], 0)) + (Nz([CCQAS_Alloc], 0)) + (Nz([CDM_Alloc], 0))
+
(Nz([NMIS_Alloc], 0)) + (Nz([TOL_Alloc], 0)) _
+ (Nz([EWSR_Alloc], 0)) > 1.000001 Then MsgBox "Allocation Can Not be
greater than 100%"
End Sub

Use the BeforeUpdate event instead of the AfterUpdate event, and set its
Cancel argument to True. The user won't be able to leave the field if the
total exceeds 1.

 

[Steve Stad]

Allen - Thank you for your reply comments. When the user enters the last
value 45% in text4 it will FORCE the user to stop, clear text4, check all
boxes, and go back and re-enter (and re calculate) the previous box(es) so
the user can not proceed until the sum is < or = to 100%. The table
validation method with conditional RED formatting will show RED but lets the
user get to the end of the form and then says there is a problem w/the
percentages. Less intrusive but may not be as effective if there are several
more fields after the percentages. Not sure if there is a standard/accepted
approach or not.

Steve, I think this approach is unproductive and frustrating.

The main thing is that the user can't save the *record* if the sum is more
than 100%. Fair enough, and the Validation Rule prevents that.

Now you're trying to not let the user out of the box until they get the
value right, but the problem may be in one of the other boxes, so they need
to get out of the box to fix it. For example, say the enters this:
Text0: 50% (should have been 5%)
Text1: 25%
Text2: 25%
Text4: 45%
When they enter the 45% in the final box, if you cancel the BeforeUpdate
event of that box, they now can't get out to go back and fix the Text0. They
have to figure out that they must set Text4 to zero, then go back to Text0
and change it to 5%, and then go back to Text4 to enter the right amount.

It would be a much less frustrating interface if you just had a calculated
total that went red to indicate a problem, and the validation rule that
prevents the record being saved, without this code to prevent them getting
to the box they need to correct.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

John,

Can you provide a little more detail regarding 'cancel = true'. I tried
this code but now the cursor will not move off of the PSR_Alloc field at
all.
Sorry I am not a programmer so I need a little more detail.

Private Sub PSR_Alloc_BeforeUpdate(Cancel As Integer)
If (Nz([PSR_Alloc], 0)) + (Nz([CCQAS_Alloc], 0)) + (Nz([CDM_Alloc], 0)) +
(Nz([NMIS_Alloc], 0)) + (Nz([TOL_Alloc], 0)) _
+ (Nz([EWSR_Alloc], 0)) > 1.000001 Then MsgBox "Allocation Can Not be
greater than 100%"
Cancel = True
End Sub

On Mon, 1 Mar 2010 08:24:11 -0800, Steve Stad

Allen - this code works for a message box if sum of allocation is >
100% -
but how can I stop/halt input and force user to change alloc numbers to
equal
less than 100%.

Private Sub CDM_Alloc_AfterUpdate()
If (Nz([PSR_Alloc], 0)) + (Nz([CCQAS_Alloc], 0)) + (Nz([CDM_Alloc], 0))
+
(Nz([NMIS_Alloc], 0)) + (Nz([TOL_Alloc], 0)) _
+ (Nz([EWSR_Alloc], 0)) > 1.000001 Then MsgBox "Allocation Can Not be
greater than 100%"
End Sub

Use the BeforeUpdate event instead of the AfterUpdate event, and set its
Cancel argument to True. The user won't be able to leave the field if the
total exceeds 1.

.