Yearly total on main form from subform

[thart21]

I have scoured the forum but can't seem to find a solution that works for this. I have a main form with a subform. On the subform I have a text box that sums the total "Hours" for each employee for training. This is named "TotalHours". Below is my code for the text box on the main form. This gives me a grand total for each employee, but I want it for the current year only, as they have to have 40 hours a year of training. How can I set the criteria to be between 1/1/2003 and 12/31/2003?


=Nz([Combined Subform].Form!TotalHours,0)

Thanks,

Toni

 

[WayneRyan]

Toni,

It sounds like you will have to use a DSum
function:

Me.YourTextBox = Nz(DSum("[HourField]", _
                         "YourTable", _
                         "[EmpID] = " & Me.EmpID & " And " & _
                         "[TrainingDate] Between #12/31/02# And #1/1/04#")

You can put that in the OnCurrent event of your Main form.

Just a rough idea to get you started.

hth,
Wayne