Calc Weekly and Monthly Totals in a Form (Access 97)

Topic

New Reply

I am creating a form which will allow a user to record his hours worked each day. I would like to create an expression in the Page Footer that calculates the week-to-date and month-to-date totals from that field. Is there an expression that could be built that would identify the work week and holidays to include in the calculation of a RunningSum?

Reply | Quote

Replies

I presume this is a form that is bound to either a table or a query that is based on the table where hours-worked records are stored. Is a single record displayed, or is it a continuous form that displays several days in a tabular format? Holidays are tricky things as they are typically unique to the company and location, so most people solve that problem by creating an hours worked for the holiday record and give it some sort of special code to indicate holiday “work” as opposed to regular work. Is overtime a concern here, i.e. something out of the normal workweek?

For the totals in the footer, there are several options. One would be to use separate DSum functions that references queries that give you only the records for the current week and the current month. Another would be to have your continuous form display either the current week records or the current month records and use a Sum function in the footer to add up those values. A third would be to create unbound text boxes for each and run some VBA behind the form each time it is opened or updated to recalculate the values. Hope this gives you some ideas to start with – post back and let us know how you proceed.

Reply | Quote