Sum Subfrm with condition

[Guest]

Hi,
I have main from [frmOrders] and Subform [frmOrderDetailsSubform]
On on the subform I have fields [Qty] and [Product].

On the main form, I have an unbound txtfield that I would like to show the
sum of [Qty] where the name of the value of [Product] begins with "F"

Below is the 'RecordSource' line that I have on the unbound txtbox on the
mainform, but Im gettin #Error in when I view in Form View... What am I doin
wrong or is that how it is done? Thanks for any help.

=Sum(IIf(Forms!frmOrders!frmOrderDetailsSubform.Form!Product Like
"F%",Forms!frmOrders!frmOrderDetailsSubform.Form!Qty,0))

 

[Marshall Barton]

I have main from [frmOrders] and Subform [frmOrderDetailsSubform]
On on the subform I have fields [Qty] and [Product].

On the main form, I have an unbound txtfield that I would like to show the
sum of [Qty] where the name of the value of [Product] begins with "F"

Below is the 'RecordSource' line that I have on the unbound txtbox on the
mainform, but Im gettin #Error in when I view in Form View... What am I doin
wrong or is that how it is done? Thanks for any help.

=Sum(IIf(Forms!frmOrders!frmOrderDetailsSubform.Form!Product Like
"F%",Forms!frmOrders!frmOrderDetailsSubform.Form!Qty,0))

The aggregate functions (Count, Sum, etc) only operate on
fields in a form's record source table/query. They are
unaware of control on the form.

To get this to work, the subform must do the Sum in a text
box (name it txtTotalF) its form footer section. Use an
expression like:
=Sum(IIf(Product Like "F*",Product, 0))

Then the main form can display the total using a text box
with the expression:
=frmOrderDetailsSubform.Form!txtTotalF

 

[Guest]

I have main from [frmOrders] and Subform [frmOrderDetailsSubform]
On on the subform I have fields [Qty] and [Product].

On the main form, I have an unbound txtfield that I would like to show the
sum of [Qty] where the name of the value of [Product] begins with "F"

Below is the 'RecordSource' line that I have on the unbound txtbox on the
mainform, but Im gettin #Error in when I view in Form View... What am I doin
wrong or is that how it is done? Thanks for any help.

=Sum(IIf(Forms!frmOrders!frmOrderDetailsSubform.Form!Product Like
"F%",Forms!frmOrders!frmOrderDetailsSubform.Form!Qty,0))

The aggregate functions (Count, Sum, etc) only operate on
fields in a form's record source table/query. They are
unaware of control on the form.

To get this to work, the subform must do the Sum in a text
box (name it txtTotalF) its form footer section. Use an
expression like:
=Sum(IIf(Product Like "F*",Product, 0))

Then the main form can display the total using a text box
with the expression:
=frmOrderDetailsSubform.Form!txtTotalF

Hi Marsh.
Thanks, I have done a Sum IIF on the subform like you said =Sum(IIf(Product
Like "F*",Product, 0)) and its working now but now I got an unbound form
on the main form which im trying to get to show the result of the sum
function on the subform...
and I got this ... =frmOrderDetailsSubform.Form!txtQtySubtotal getting #Name
What am I doing wrong here?

 

[Marshall Barton]

I have main from [frmOrders] and Subform [frmOrderDetailsSubform]
On on the subform I have fields [Qty] and [Product].

On the main form, I have an unbound txtfield that I would like to show the
sum of [Qty] where the name of the value of [Product] begins with "F"

Below is the 'RecordSource' line that I have on the unbound txtbox on the
mainform, but Im gettin #Error in when I view in Form View... What am I doin
wrong or is that how it is done? Thanks for any help.

=Sum(IIf(Forms!frmOrders!frmOrderDetailsSubform.Form!Product Like
"F%",Forms!frmOrders!frmOrderDetailsSubform.Form!Qty,0))

The aggregate functions (Count, Sum, etc) only operate on
fields in a form's record source table/query. They are
unaware of control on the form.

To get this to work, the subform must do the Sum in a text
box (name it txtTotalF) its form footer section. Use an
expression like:
=Sum(IIf(Product Like "F*",Product, 0))

Then the main form can display the total using a text box
with the expression:
=frmOrderDetailsSubform.Form!txtTotalF

Thanks, I have done a Sum IIF on the subform like you said =Sum(IIf(Product
Like "F*",Product, 0)) and its working now but now I got an unbound form
on the main form which im trying to get to show the result of the sum
function on the subform...
and I got this ... =frmOrderDetailsSubform.Form!txtQtySubtotal getting #Name

Well, to overstate the obvious, #Name? means that Access
can't find one of the names in your expression. Double
check the name of the subform **control** ,which might be
different than the name of the form it is displaying.

If the subform has no data or if the subform text box name
were spelled wrong, you would get #Error

 

[Guest]

Thanks Marsh,
Yes, I renamed subform after creation but forgot to have the name property
updated to its current name and that was what it was... its all good now...
Thanks again...
PS: This is a very helpful group