Form Textbox with Total

[gsandy]

I have a timesheet table (tblHourEnter) which has a staff field (Staff_ID), week number field (Week_No) and hours field (Hours_Worked).


The data is entered through a form (frmHourEnter) which has a staff textbox (Staff_ID), week number textbox (Week_No) and hours textbox (Hours_Worked).


I wish to add another textbox (HrsTotal) that would show the sum of all the hours (from the table) which is dependent upon both the Staff_ID and Week_No entries showing on the current form. I.e. the sum of hours is restricted to both staff member and week number showing on frmHourEnter.


This total can be generated from a command button.

Can this be done and how? Thanks Sandy

 

[JHB]

Set the controlsource for the control "HrsTotal" like below:
Depending of from where you're in the would, ";" should be ","

=DSum("HourWorked"[B][COLOR=Red];[/COLOR][/B]"tblHourEnter"[B][COLOR=Red];[/COLOR][/B]"Staff_ID =" & [Staff_ID] & " AND Week_No=" & [Week_No])

 

[gsandy]

Hi JHB, that worked a treat, thanks for your help, Sandy

 

[JHB]

You're welcome!

 

[gsandy]

I did have alter the code slightly:

  =DSum("Hours_Worked","tblHourEnter","Staff_ID =" & forms![frmHourEnter]! [Staff_ID] & " AND Week_No=" & forms![frmHourEnter]! [txtDateCalc])