I have VBA weighted average custom functions that work great on entire tables. I'm wondering if there is an object that defines the portion of the table segregated by groups setup in the detail portion of a form?
Here's the wtgavg function I use for whole tables. Thanks!
Exp = Numbers to average
Crit = Weighting
Dmn = Table/Query name
Public Function WtgAvg(Exp As String, Dmn As String, Crit As String) As Double
On Error GoTo errorhndl:
Dim Num As Double
Dim Den As Double
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(Dmn)
Num = 0
Den = 0
Do Until rst.EOF
Num = Num + CDbl(rst.fields(Exp).Value) * CDbl(rst.fields(Crit).Value)
Den = Den + CDbl(rst.fields(Crit).Value)
WtgAvg = Num / Den
Set rst = Nothing
Last edited by AhrenL; 11-11-09 at 15:36.
Reason: Set as Question
there is nothing defined as a 'group' within a form. it is within reports.
NTC made the very point days ago. This is trivial in reports. All the hoops we have been jumping through are due to the "limitations" of forms. Details like whether or not you are using forms or reports make all the difference in the world.
Take the bit out of the select clause of the SQL I provided and put it in a textbox in your report grouping. Done.
Sorry.. I really wasn't clear (Though it was totally clear in my head when I replied to NTC). Not a complete waste of time as I've definitely learned more about SQL, and I'll have to do this in a form sooner than later in any case.
That's working great however. Sorry for the extra trouble.