SUM of field in a continuous subform (1 Viewer)

[Ken Sheridan]

have tried as suggested
=Sum(txtQuantity * txtSellAUD * (txtSaleProbability / 100))...still getting an ERROR message
I have checked names etc. I have pasted direct from your page... no luck
What am I doing wrong???

Is the control in the subform's footer or in the parent form? If the latter then you should do as RonPaii described in post #15 and sum the values in a hidden control in the subform's footer, and reference that control in the ControlSource property of an unbound control in the parent form. Alternatively you can cut out the middleman and call the DSum function in the ControlSource property of a text box in the parent form, restricting the rows summed to those with the parent form's primary key value in the relevant foreign key column.

 

[Bobp3114]

Is the control in the subform's footer or in the parent form? If the latter then you should do as RonPaii described in post #15 and sum the values in a hidden control in the subform's footer, and reference that control in the ControlSource property of an unbound control in the parent form. Alternatively you can cut out the middleman and call the DSum function in the ControlSource property of a text box in the parent form, restricting the rows summed to those with the parent form's primary key value in the relevant foreign key column.

I tried working the probability on the form first....=Sum([txtQuantity]*[txtSellAUD]*[txtProbabilityWorked])
No matter what I type I always get the square brackets I thought this meant a field rather than a control is this the problem? If so how do I stop them?
Yes the control is on the subform footer.

 

[DHookom]

I suggested in post #17 one possible solution that removes the control names. You haven’t provided enough information about your recordsources and requirements to get more specific.

 

[Bobp3114]

Are txtQuantity, txtSellAUD, and txtSaleProbability controls?

Yes, all textboxes. txtQuantity is simply a number, txtsellAUD is adjusted according to the currency rate, txtSale Probability is adjusted according to the way the possible sale continues (eg Order placed may be 80 out of 100 (80%))

 

[Bobp3114]

One key to resolving your issue involves the control sources of your "txt" boxes. For instance, if you have a text box txtTotal in your detail section with a control source of:
Name: txtTotal
Control Source: =[Cost] * [Qty] * (1-[Discount])
To Sum this in a form header or footer, you could use:
Name: txtSumTotal
Control Source: =Sum([Cost] * [Qty] * (1-[Discount]))

You can't sum the control =Sum(txtTotal)

Sorry mate I am confused. Is not your
Name: txtSumTotal
Control Source: =Sum([Cost] * [Qty] * (1-[Discount]))
the same as my =Sum(txtQuantity * txtSellAUD * (txtSaleProbability / 100)) ?

 

[CJ_London]

. Is not your
Name: txtSumTotal
Control Source: =Sum([Cost] * [Qty] * (1-[Discount]))
the same as my =Sum(txtQuantity * txtSellAUD * (txtSaleProbability / 100)) ?

no - the first references the recordset, yours references the form controls

 

[Gasman]

Getting conflicting information in this thread?

Post #2

You can’t sum a control. You can only sum fields and expressions. What is the control source of [txtTagRecord]?

From post #16

=Sum(txtQuantity * txtSellAUD * (txtSaleProbability / 100))

 

[DHookom]

Getting conflicting information in this thread?

Post #2

From post #16

Exactly! I usually defer to Ken but his summing of a control seems I believe is wrong. Ken has never been wrong before

 

[Ken Sheridan]

Ken has never been wrong before

I'm sure that's not true, but I certainly was in this case! I'd completely missed that the references seem to be to controls, not columns. Mea culpa, mea maxima culpa. Specsavers have been nagging me to get an eye test for some time. Maybe they have a point.

 

[Bobp3114]

I thank you all for your help but my brain must be mush because I can't get it to work. I got help from my younger members and AI and with some effort came up with; =Sum(IIf([PartPreference]="Preferred",Nz([EstimatedAnnualQuantity],0)*Nz([SellingPrice],0)*Nz([SaleExchangeRate],0)*(Nz([Probability],0)/100),0))
Note this includes a little more in the way of functions.
I welcome your comments

 

[CJ_London]

you get an error? wrong result?

and where is the sum calculation? in the subform footer or the mainform footer? somewhere else?

have you verified all the fields exist and are spelt correctly?

have you verified all the fields in the table are specified as numeric (and not text)

is your partpreference field a lookup field in the table

have you tried putting the formula (excluding the sum) in your subform recordsource as an additional field - then simpler to sum

 

[Gasman]

I thank you all for your help but my brain must be mush because I can't get it to work. I got help from my younger members and AI and with some effort came up with; =Sum(IIf([PartPreference]="Preferred",Nz([EstimatedAnnualQuantity],0)*Nz([SellingPrice],0)*Nz([SaleExchangeRate],0)*(Nz([Probability],0)/100),0))
Note this includes a little more in the way of functions.
I welcome your comments

You appear to be summing the source fields and not the controls, as advised way back in post #2 ?

 

[Ken Sheridan]

=Sum(IIf([PartPreference]="Preferred",Nz([EstimatedAnnualQuantity],0)*Nz([SellingPrice],0)*Nz([SaleExchangeRate],0)*(Nz([Probability],0)/100),0))

1. Are all of the objects referenced in the expression columns in the subform's RecordSource table or query, as should be the case?

2. Are any of the columns 'look-up fields'? If so the value in the column is not the text you see, e.g. 'Preferred' in the case of the PartPreference column, but a hidden numeric value which references the value of the numeric primary key column of the relevant referenced table.

3. Is the expression the ControlSource of a control in the subform, as should be the case, or a control in the parent form?