Advanced Subform Calculations

[karatelung]

I have a subform, "subfrmTraining," which has an unbound text box in the
footer, "txtTotalHrs" with the data source "=Sum([Hrs])" to give me the
total hours.

The form, "frmFosterInfo", has a text box, "Text35," with the data source
"=[subfrmTraining].Form!txtHrsTotal" which gives me the Sum of [Hrs] showing
in the subform.

My problem is that I want two more unbound text boxes on the form that Sum
only certain records in the subform. One text box should Sum only the [Hrs]
with [DateTraining] between [CurrentLicenseDate] and [LicenseReview] dates.
The other should Sum only the [Hrs] with [DateTraining] > [LicenseReview].

subfrmTraining contains the fields: [DateTraining] and [Hrs]
frmFosterInfo contains the fields: [CurrentLicense] and [LicenseReview]*

*[LicenseReview] is an unbound text box that displays the date one year
greater than the date in [CurrentLicense]. For the OnCurrent event in
frmFosterInfo I have Me.LicenseReview = DateAdd("yyyy", 1,
Me.CurrentLicensce).

Hopefully I explained this well enough.

Help on this matter is greatly appreciated. I'm sure the pros in this group
can figure this out about 1000x faster than I can.

TIA,

Richard Bernacki

 

[Jack Cowley]

Search Help for the DSum() function.

 

[karatelung]

Okay, got it. I guess I just needed to know it was possible to get what I wanted with DSum.

For anyone who may need to refer to a calculated (or any) field on a form using domain aggregate functions, here's what I used for the "1stHalf" field. Note that [AutoNumber] and [IndFosterID] are the links between the form and sub form:

=DSum("[Hrs]","qrySubTraining","[IndFosterID] = [Forms]![frmIndFosterInfo].[AutoNumber]" & "AND [DateTraining] < [Forms]![frmIndFosterInfo].[LicenseReview]")

Then for the "2ndHalf" field, I just subtracted the "1stHalf" field from the "TotalHrs" field.

Thanks for the DSum tip.