Averaging Selected Records

  • Thread starter Thread starter Lythandra
  • Start date Start date
L

Lythandra

Hi,

I have a continuous form with lots of data on it.

In the form footer I would like to average certain fields by checking a
checkbox next to the fields (in the detail section) I would like included in
the average.

I am having a brain fart on this and just not getting it to average the
selected records.

Can anyone point me in the right direction?

Thanks
 
Lythandra said:
Hi,

I have a continuous form with lots of data on it.

In the form footer I would like to average certain fields by checking a
checkbox next to the fields (in the detail section) I would like included
in
the average.

I am having a brain fart on this and just not getting it to average the
selected records.

Can anyone point me in the right direction?
Click to expand...


You don't say exactly where you're getting stuck. The first possible
sticking point is that the check box has to be bound to a field in the
table -- if it isn't, it will have the same value for all records.

Assuming that you do have a yes/no field that is True for the selected
records and False for the unselected ones, I would think that your text box
could use a calculated controlsource involving the DAvg() function, along
these lines:

=DAvg("FieldTBeAveraged", "TableName", "IsSelected<>0")

Now, you still will have two possible problems. First, just because you
check the check box on a record, that doesn't mean the record was saved with
that updated value. So you need to force the record to be saved when the
check box is updated. You could use the control's AfterUpdate event to do
that:

Private Sub chkIsSelected_AfterUpdate()

Me.Dirty = False

End Sub

The remaining possible problem is that you *may* need to force the text box
to be requeried after the record is saved. I'm not sure whether you will or
not. If you do, you can use the form's AfterUpdate event to do that:

Private Sub Form_AfterUpdate()

Me!txtAverage.Requery

End Sub

I hope these thoughts get you on the right road.
 
That got me on the right path, thanks.

Dirk Goldgar said:
You don't say exactly where you're getting stuck. The first possible
sticking point is that the check box has to be bound to a field in the
table -- if it isn't, it will have the same value for all records.

Assuming that you do have a yes/no field that is True for the selected
records and False for the unselected ones, I would think that your text box
could use a calculated controlsource involving the DAvg() function, along
these lines:

=DAvg("FieldTBeAveraged", "TableName", "IsSelected<>0")

Now, you still will have two possible problems. First, just because you
check the check box on a record, that doesn't mean the record was saved with
that updated value. So you need to force the record to be saved when the
check box is updated. You could use the control's AfterUpdate event to do
that:

Private Sub chkIsSelected_AfterUpdate()

Me.Dirty = False

End Sub

The remaining possible problem is that you *may* need to force the text box
to be requeried after the record is saved. I'm not sure whether you will or
not. If you do, you can use the form's AfterUpdate event to do that:

Private Sub Form_AfterUpdate()

Me!txtAverage.Requery

End Sub

I hope these thoughts get you on the right road.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top