Totals from subforms not calculating (1 Viewer)

[cclark9589]

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.

 

[CEH]

Take a look at this link....may solve your problem.
http://msdn.microsoft.com/en-us/library/aa198802(office.10).aspx

 

[boblarson]

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))

 

[cclark9589]

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?

 

[CEH]

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.