Adding two subtotals together.

[Flopbot]

I have a Subform with multiple columns in it. One column is calculated and
is subtotaled in the Form Footer. Another column is used to log any extra
hours worked by a volunteer that day. It too is being subtotaled in the Form
Footer.

Here are the two subtotals:

=Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])

=Sum([Other Hours])

I want to add these two subtotals together in a control box on the main form
so I can see at a glance the total number of hours volunteered by each
individual volunteer.


This is one of my failed attempts which results in “The expression you
entered contains invalid syntax.â€:

=(([Volunteer Hours Subform]!Subtotal)+Nz([Extra Hours]))+(([Volunteer Hours
Subform]!Subtotal Other Hours)+Nz([Extra Hours]))

Any ideas? Thanks!

 

[Arvin Meyer [MVP]]

What is: [Extra Hours] where did it come from?

Try:

=(([Forms]![YourMainFormName]![Volunteer Hours
Subform].Form!Subtotal)+Nz([Extra
Hours]))+(([Forms]![YourMainFormName]![Volunteer Hours
Subform].Form!Subtotal Other Hours)+Nz([Extra Hours]))

I left [Extra Hours] in, but you may not get an accurate total.

 

[Flopbot]

Thanks Arvin! Your comment about [Extra Hours] made me realize that I could
completely get rid of that function. It will be redundant now and in the
process it lets me get rid of a really annoying rogue field in an unrelated
table.

It works! I had to slightly modify yours by adding [ ] around the Subtotals
& Nz() around each section and it works like a charm. Here’s my final.
Thanks!

=Nz((Forms![Active Volunteer Frm]![Volunteer Hours
Subform].Form!Subtotal))+Nz((Forms![Active Volunteer Frm]![Volunteer Hours
Subform].Form![Subtotal Other Hours]))

What is: [Extra Hours] where did it come from?

Try:

=(([Forms]![YourMainFormName]![Volunteer Hours
Subform].Form!Subtotal)+Nz([Extra
Hours]))+(([Forms]![YourMainFormName]![Volunteer Hours
Subform].Form!Subtotal Other Hours)+Nz([Extra Hours]))

I left [Extra Hours] in, but you may not get an accurate total.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a Subform with multiple columns in it. One column is calculated and
is subtotaled in the Form Footer. Another column is used to log any extra
hours worked by a volunteer that day. It too is being subtotaled in the
Form
Footer.

Here are the two subtotals:

=Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])

=Sum([Other Hours])

I want to add these two subtotals together in a control box on the main
form
so I can see at a glance the total number of hours volunteered by each
individual volunteer.


This is one of my failed attempts which results in "The expression you
entered contains invalid syntax.":

=(([Volunteer Hours Subform]!Subtotal)+Nz([Extra Hours]))+(([Volunteer
Hours
Subform]!Subtotal Other Hours)+Nz([Extra Hours]))

Any ideas? Thanks!