Suming Fields

[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

 

[Guest]

Try and write in the ControlSource of the sum field

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

 

[Guest]

Tried that but it keeps coming up with an error.

I Need something that will work with about 10 fields

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

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

 

[Guest]

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

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

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

 

[Steve Schapel]

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.