Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / January 2008

Tip: Looking for answers? Try searching our database.

Averaging Selected Records

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Lythandra - 08 Jan 2008 21:53 GMT
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
Dirk Goldgar - 08 Jan 2008 22:24 GMT
> Hi,
>
[quoted text clipped - 9 lines]
>
> Can anyone point me in the right direction?

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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Lythandra - 09 Jan 2008 19:12 GMT
That got me on the right path, thanks.

> > Hi,
> >
[quoted text clipped - 44 lines]
>
> I hope these thoughts get you on the right road.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.