Sum columns in a report

[djsdaddy]

Good Day All, First let me apologize for posting this originally in the Access Article Forum. I recognized the mistake and I am now posting it in the correct Forum.

I have a report that shows the hours that are worked by employees, who get paid on a biweekly basis. There is a "Total Hours Worked" column that is further divided into "Week1" and "Week2." It looks like this example:

[HTML]Total Hours Worked
Week1 Week2
8.00
8.00
8.00
9.00
11.00
3.50
6.00
6.00
6.00
6.00
----------------
44.00 27.50[/HTML]

The reason I have the "Total Hours Worked" column divided into Week1 and Week2 is because I want to be able to calculate whether an employee has worked more than 40 hours per week for Overtime purposes.

The control source for the Week1 work hours is a field named txtWeek1 and here is the code that I use

=IIF([DateIn]-Forms!frmDates4Pay.txtDateBegin<7,(Format([TimeWorked],"#.00"))," ")

And the control source for the Week2 work hours is a field named txtWeek2 and here is the code that I use

=IIF([DateIn]-Forms!frmDates4Pay.txtDateBegin>6,(Format([TimeWorked],"#.00"))," ")

The code described above works just as I expect. However, I want to be able to add the columns separately, and my problem is that the fields that I am using to calculate the sum of the time worked is calculating both weeks for each field. I am using a simple Sum Statement for both total fields:

=Format(Sum([TimeWorked]),"#.00")
 

Is there a way to sum only those hours under Week1 in a total field separately from the hours in Week2? Those fields are named txtSumWeek1 and txtSumWeek2. As it stands now, both of these fields has the two weeks total displayed. FYI, the user inputs the payperiod begin and end dates from a form named frmDates4Pay and the click event to preview the form filters all days worked to between date begin and date end of the payperiod.

I am running XP and using Access 2002 in 2000 mode.

Any assistance will be greatly appreciated,

David

[JConsulting]

=sum([txtSumWeek1]) and sum([txtSumWeek2])

[djsdaddy]

Thanks JConsulting, I tried your suggestion and I am being asked to enter a parameter value for txtWeek1. I don't know if this makes a difference, but txtWeek1 and txtWeek2 are controls on the report.

Thanks again,

David

[JConsulting]

Thanks JConsulting, I tried your suggestion and I am being asked to enter a parameter value for txtWeek1. I don't know if this makes a difference, but txtWeek1 and txtWeek2 are controls on the report.

Thanks again,

David

I wasn't really clear I think..

You're referencing these on the section's footer in your report correct?

=sum([txtWeek1])
=sum([txtWeek2])

[djsdaddy]

That's correct, but I am now trying to get those figures in the query also. I've got to leave and work a short evening shift tonight, so it will probably be tomorrow morning before I can give you some feedback on my progress

[djsdaddy]

I wasn't really clear I think..

You're referencing these on the section's footer in your report correct?

=sum([txtWeek1])
=sum([txtWeek2])

Yes I am referencing these in the section's footer section of the report, and I would think that that code would work, but I came up with the following error:

[HTML]This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.[/HTML]

I calculated the figures for txtWeek1 and txtWeek2 in the query underlying the report and the numbers are reflected in the report just as I had expected. This is why the error is so baffling to me.

Any further suggestions?

Thanks in advance,

David

[JConsulting]

Yes I am referencing these in the section's footer section of the report, and I would think that that code would work, but I came up with the following error:

[HTML]This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.[/HTML]

I calculated the figures for txtWeek1 and txtWeek2 in the query underlying the report and the numbers are reflected in the report just as I had expected. This is why the error is so baffling to me.

Any further suggestions?

Thanks in advance,

David

check the field list and the name property of the boxes...make sure the names are correct.

Try moving the totals onto the page..or the report footer. see if they calculate there.
J