Sum() in Form footer (again)

[Guest]

Saw an existing thread - similar problem, but solution didn't work
I want user to select records which are used to calc average; min; max
values in form footer
I use Continuous forms; In Detail I have an unbound text box Text10
=IIf([include]=Yes,[rate],0) (this works ie User ticks/unticks & correct
rate is shown
This gives me a set of data which I want to perform calcs on in form footer -
=Avg([text10]) - gives an #error
I have Avg([rate]) in footer which works until I add in unbound text box
with =Avg([Text10]) then all boxes show #error message
Any ideas?

 

[BruceM]

I believe you need to use the average of the calculation used in Text10. An
unbound text box can't be used for such things as Sum and Average of all
records.
=Avg(IIf([include]=Yes,[rate],0))

 

[Guest]

Thanks - nearly works - this averages selected records/total records ie if I
have 20 records & want to exclude 3 the average is Sum(17 records)/Count(20
records). I need the average of 17

I believe you need to use the average of the calculation used in Text10. An
unbound text box can't be used for such things as Sum and Average of all
records.
=Avg(IIf([include]=Yes,[rate],0))

Saw an existing thread - similar problem, but solution didn't work
I want user to select records which are used to calc average; min; max
values in form footer
I use Continuous forms; In Detail I have an unbound text box Text10
=IIf([include]=Yes,[rate],0) (this works ie User ticks/unticks & correct
rate is shown
This gives me a set of data which I want to perform calcs on in form
footer -
=Avg([text10]) - gives an #error
I have Avg([rate]) in footer which works until I add in unbound text box
with =Avg([Text10]) then all boxes show #error message
Any ideas?

 

[BruceM]

On what basis do you exclude the three records?

Thanks - nearly works - this averages selected records/total records ie if
I
have 20 records & want to exclude 3 the average is Sum(17
records)/Count(20
records). I need the average of 17

I believe you need to use the average of the calculation used in Text10.
An
unbound text box can't be used for such things as Sum and Average of all
records.
=Avg(IIf([include]=Yes,[rate],0))

Saw an existing thread - similar problem, but solution didn't work
I want user to select records which are used to calc average; min; max
values in form footer
I use Continuous forms; In Detail I have an unbound text box Text10
=IIf([include]=Yes,[rate],0) (this works ie User ticks/unticks &
correct
rate is shown
This gives me a set of data which I want to perform calcs on in form
footer -
=Avg([text10]) - gives an #error
I have Avg([rate]) in footer which works until I add in unbound text
box
with =Avg([Text10]) then all boxes show #error message
Any ideas?

 

[Guest]

Simple tick box (needs to be simple) - no defined criteria are to be set, all
User choice

On what basis do you exclude the three records?

Thanks - nearly works - this averages selected records/total records ie if
I
have 20 records & want to exclude 3 the average is Sum(17
records)/Count(20
records). I need the average of 17

I believe you need to use the average of the calculation used in Text10.
An
unbound text box can't be used for such things as Sum and Average of all
records.
=Avg(IIf([include]=Yes,[rate],0))

Saw an existing thread - similar problem, but solution didn't work
I want user to select records which are used to calc average; min; max
values in form footer
I use Continuous forms; In Detail I have an unbound text box Text10
=IIf([include]=Yes,[rate],0) (this works ie User ticks/unticks &
correct
rate is shown
This gives me a set of data which I want to perform calcs on in form
footer -
=Avg([text10]) - gives an #error
I have Avg([rate]) in footer which works until I add in unbound text
box
with =Avg([Text10]) then all boxes show #error message
Any ideas?

 

[BruceM]

The simplest would probably be to requery the form based on the value in the
check box (assuming the check box is bound to a field) in order to limit the
recordset to just those records you want to average, then use the same
formula as has been posted. Other than that one option is to leave the
Average text box (I will call it txtAvg) unbound, and use something like
this in an event procedure (a command button, maybe):

Dim strCriteria as String

strCriteria = "[YesNoField] =" & False

Me.txtAvg = DAvg("FieldName","tblName",strCriteria)

Simple tick box (needs to be simple) - no defined criteria are to be set,
all
User choice

On what basis do you exclude the three records?

Thanks - nearly works - this averages selected records/total records ie
if
I
have 20 records & want to exclude 3 the average is Sum(17
records)/Count(20
records). I need the average of 17

:

I believe you need to use the average of the calculation used in
Text10.
An
unbound text box can't be used for such things as Sum and Average of
all
records.
=Avg(IIf([include]=Yes,[rate],0))

Saw an existing thread - similar problem, but solution didn't work
I want user to select records which are used to calc average; min;
max
values in form footer
I use Continuous forms; In Detail I have an unbound text box Text10
=IIf([include]=Yes,[rate],0) (this works ie User ticks/unticks &
correct
rate is shown
This gives me a set of data which I want to perform calcs on in form
footer -
=Avg([text10]) - gives an #error
I have Avg([rate]) in footer which works until I add in unbound text
box
with =Avg([Text10]) then all boxes show #error message
Any ideas?