Totals from subforms not calculating

cclark9589

Registered User.
Local time
Today, 16:48
Joined
Sep 22, 2008
Messages
79
I have two subforms residing on tabs. Each subform has a total for that tab that displays the total hours for that tab. Below the subforms is another control that returns a grand total of the two tabs. One is for billable hours and the other for non-chargeable hours. Whenever new data is entered the totals update fine except for one minor thing.

If I have no hours entered for a particular time period on the non-chargeable tab, the grand total remains blank. I can put a zero in and then the grand total computes. Take it out, it disappears. I've tried using an IIF statement with and without Nz but no go.

Here's the code I currently have in the control source:
=IIf([TC_NChgSubform].[Form]![NCHours]="0",[TC_HrsSubform].[Form]![Total Hours Worked],[TC_HrsSubform].[Form]![Total Hours Worked]+[TC_NChgSubform].[Form]![NCHours])

I've tried different combinations: without the "", "" only, etc. I have a headache again and can't figure this out and haven't found anything pointing the way.

Help for this simpleton would be appreciated.
 
See if this helps:

=IIf([TC_NChgSubform].[Form]![NCHours]=0,[TC_HrsSubform].[Form]![Total Hours Worked],Nz([TC_HrsSubform].[Form]![Total Hours Worked],0)+Nz([TC_NChgSubform].[Form]![NCHours],0))
 
As usual Bob you were spot on. I had tried all sorts of combinations but obviously not that one. I must have spent at least an hour trying variations/combinations and couldn't get it to function properly. Let me guess, 20 seconds tops and you had the answer?

CEH: thanks for the link. Dry reading but informative nonetheless. Another shortcut hits the desktop.

Speaking of that link. Instead of putting a function into the control source, could I put it into VB as a Before/AfterUpdate? And if so, is there any advantage in doing so?
 
Last edited:
I think after you gain more and more experience with Access you will begin using more and more code.... Your IIF will become an "If....then....Else" or perhaps a "Switch" function (see this link.. http://www.techonthenet.com/access/functions/advanced/switch.php )
As for the technical aspects of it, Bob could probably answer that one better than I could.
 

Users who are viewing this thread

Back
Top Bottom