How to add values from multiple subforms question!

[MDDoc]

Hi there,

I am somewhat new to MS Access and I am trying to figure out how to add times together from multiple subforms and display the data on the main form.

Just for backround, I am creating a timesheet to be used by our workshop employees. The form is set up in such a way that there is a combo box where they can select their name which rotates the record so all the data is specific to their id, and then there is a tab box with a tab for each day of the week each containing its own subform with combo boxes to select the project they worked on, text boxes to enter the start time and stop time for that project, and then 2 other text boxes: one that calculates time worked on that particular project and then one that calculates the total time worked for the day since they typically work on more than one project in a day. So what I need to do is to take the values from the total daily hours boxes and add them all together and show the sum in a box showing the total hours worked for the week.

My most recent attempt was to simply make an expression in the control source as follows:


=[Sunday subform].[Form]![Daily Hours]+[Monday subform].[Form]![Daily Hours]+[Tuesday subform].[Form]![Daily Hours]+[Wednesday subform].[Form]![Daily Hours]+[Thursday subform].[Form]![Daily Hours]+[Friday subform].[Form]![Daily Hours]+[Saturday subform].[Form]![Daily Hours]

this is just showing up as 0:00 in the box showing weekly hours.

Anyone have any suggestions of things to try?
Thank you so much for the help!

[John_G]

There are a number of questions that arise here -

What are the data types of the Start time and Stop time, and what sort of data do they contain?
How do you calculate the values in the project Time and Total Daily hours boxes?

Have you confirmed that those boxes actually contain values when you try to calculate the Weekly total?

You have six subforms - are they all based on the same table (they should be)?
Based on what you have said, you might be able to use DSum or an aggregate (totals) query to get the data you need.

John

[MDDoc]

John,
To answer your questions:
The stop time and start time are both Medium Time data and they contain something along the time of 9:00 AM.

The project time box, which is called [HoursWorked], is calculated using the expression: =[StopTime]-[StartTime] in the control source and the Total Daily Hours boxes are calculated by doing a Sum([HourWorked]) function.
Yes These boxes always contain values when I try to calculate the weekly totals.

All seven subforms are all based on the same table called [WorkLog] which contains the Project, day worked, start time, stop time, and hours worked. The day worked is used purely to seperate out the subforms by assigning each one a different select query that searches for that day thus making sure the tabs are accurate with subforms and the subforms are all independant.

The Daily Hours and Weekly Hours are not in a tables though, do you think this is an issue? I was trying to do them purely as a read only calculation. The Daily Hours works perfectly this way.

I have tried to use the DSum function but I am not super comfortable with it and find it a little confusing, but I will try making an aggregate query and see what happens.