Calculated Control Field Criteria

A

aMack

I have the following form - trying to get a calculation based on form criteria:

Main Form = [Item #]

Detail = [Item #], [Bundle #], [Wgt] [Check Box]

I need to display "Sum of [Wgt]" but limited to the [Item #] from the main
form and only Records that the [Checkbox] is "Yes".

Any direction would be great.

Thanks
 
J

John W. Vinson

I have the following form - trying to get a calculation based on form criteria:

Main Form = [Item #]

Detail = [Item #], [Bundle #], [Wgt] [Check Box]

I need to display "Sum of [Wgt]" but limited to the [Item #] from the main
form and only Records that the [Checkbox] is "Yes".
Click to expand...

Put a calculated field in the Query upon which the form is based by typing

WgtToSum: IIF([Check Box], [Wgt], 0)

Then on the Footer of the form put a textbox with a control source

=Sum([WgtToSum])

John W. Vinson [MVP]
 
M

Marshall Barton

aMack said:
I have the following form - trying to get a calculation based on form criteria:

Main Form = [Item #]

Detail = [Item #], [Bundle #], [Wgt] [Check Box]

I need to display "Sum of [Wgt]" but limited to the [Item #] from the main
form and only Records that the [Checkbox] is "Yes".
Click to expand...


Since aggregate functions do not know about controls on the
form, you can not do the usual thing in the footer text box
with an expression like:
=Sum(IIf([Item #] = [main text box] And [Checkbox], Wgt, 0))

I think you can get the desired effect by using some code in
the main form text box's AfterUpdate event:

Dim xx As Variant
xx = [main text box]

[footer text box].ControlSource = "=Sum(IIf([Item #] =" _
& [main text box] & " And [Checkbox], Wgt, 0))

[main text box] = xx

For some unknown reason, in my test the main form text box's
value is reset when the footer text box's ControlSource is
set, so xx is used to save and restore its value.
 
A

aMack

John:

Excellent!

Thanks

--
A MACKENZIE, CMA, MBA


John W. Vinson said:
I have the following form - trying to get a calculation based on form criteria:

Main Form = [Item #]

Detail = [Item #], [Bundle #], [Wgt] [Check Box]

I need to display "Sum of [Wgt]" but limited to the [Item #] from the main
form and only Records that the [Checkbox] is "Yes".
Click to expand...

Put a calculated field in the Query upon which the form is based by typing

WgtToSum: IIF([Check Box], [Wgt], 0)

Then on the Footer of the form put a textbox with a control source

=Sum([WgtToSum])

John W. Vinson [MVP]
Click to expand...
 
Top