Sum() in Form footer (again)

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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))
 
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

BruceM said:
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))

Saintsman said:
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?
Click to expand...
Click to expand...
 
On what basis do you exclude the three records?

Saintsman said:
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

BruceM said:
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))

Saintsman said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Simple tick box (needs to be simple) - no defined criteria are to be set, all
User choice


BruceM said:
On what basis do you exclude the three records?

Saintsman said:
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

BruceM said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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)

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


BruceM said:
On what basis do you exclude the three records?

Saintsman said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top