Suming Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a form which has Various Fields with Cost values in them. There is a
tick tick box beside each field that enables the field or disables the field
depending upon the tick.

I want to add the fields up where the fields are enabled and skip the fields
which are disabled.

or

Is there a way to make the cost field revert to "0" when it is disabled.

Can someone help

Thanks
 
Try and write in the ControlSource of the sum field

=Sum(IIf([Yes No field name] = True, [Cost Field Name],0))
 
Tried that but it keeps coming up with an error.

I Need something that will work with about 10 fields



Ofer said:
Try and write in the ControlSource of the sum field

=Sum(IIf([Yes No field name] = True, [Cost Field Name],0))

--
\\// Live Long and Prosper \\//
BS"D


Andrew C said:
Hi

I have a form which has Various Fields with Cost values in them. There is a
tick tick box beside each field that enables the field or disables the field
depending upon the tick.

I want to add the fields up where the fields are enabled and skip the fields
which are disabled.

or

Is there a way to make the cost field revert to "0" when it is disabled.

Can someone help

Thanks
Click to expand...
Click to expand...
 
try this

=IIf([Yes No field name1] = True, Nz([Cost Field Name1],0),0) + IIf([Yes No
field name2] = True, Nz([Cost Field Name2],0),0) + IIf([Yes No field name3] =
True, Nz([Cost Field Name3],0),0)

--
\\// Live Long and Prosper \\//
BS"D


Ofer said:
Try and write in the ControlSource of the sum field

=Sum(IIf([Yes No field name] = True, [Cost Field Name],0))

--
\\// Live Long and Prosper \\//
BS"D


Andrew C said:
Hi

I have a form which has Various Fields with Cost values in them. There is a
tick tick box beside each field that enables the field or disables the field
depending upon the tick.

I want to add the fields up where the fields are enabled and skip the fields
which are disabled.

or

Is there a way to make the cost field revert to "0" when it is disabled.

Can someone help

Thanks
Click to expand...
Click to expand...
 
Andrew,

One approach, as you suggested, would be to set the values to 0 when the
checkbox is unticked. You could put code something like this on the
AfterUpdate event of each of the checkboxes...
If Me.CheckboxName = 0 Then
Me.TheAssociatedField = 0
End If

Then your calculation expression will, presumably, just be...
=[1stField]+[2ndField]+...+[10thField]

An alternative approach would rely on the fact that a ticked checkbox
has a value of -1 and unticked is 0.

=-([1stCheckbox]*[1stField]+[2ndCheckbox]*[2ndField]+...+[10thCheckbox]*[10thField])

This assumes that all fields will have something in them, i.e. no
blanks, otherwise your calculation will need to cater to nulls.

This proviso would also apply to...

=IIf([1stCheckbox],[1stField],0)+IIf([2ndCheckbox],[2ndField],0)+...+IIf([10thCheckbox],[10thField],0)

Having said that, I would point out that the need to do something like
this in a database is very rare, and may indicate that your table design
could do with a review.
 
Back
Top