Sum values in each row

[megmoo75]

Hello - I think this is probably quite easy, but I just can't seem to get it to work. I need to sum the values of four items in each row as shown below:

Task Total Person1 Person2 Person3 Person4
task1 5 4 1
task2 10 3 4 3

What I can't get is the Total column, which should be the sum of Person1 - Person 4. I have two problems right now:
1. It's only summing those rows where every person has time attributed that that task
2. It's doing a running total rather than just the sum for that row.

The control source of my Total textbox for the Total column is currently: [Person1] + [Person2] + [Person3] + [Person4].

Can anyone tell me how I can get it to sum even when not all people have time for a task and how to stop it from continuing to sum using the line above?

Thanks in advance.

 

[megmoo75]

OK, I figured it out and thought I'd post the answer in case others search for an answer to this problem. There may be a much easier way, but this is what I did to fix it.

I created an invisible text box called txtZero on my report and set the value of it to 0 (zero).

Then, the control source of my text box that should total the time spent per task for each person uses this calculation:
=IIf(IsNumeric([Person1]),[Person1],[txtZero])+
IIf(IsNumeric([Person2]),[Person2],[txtZero])+
IIf(IsNumeric([Person3]),[Person3],[txtZero])+
IIf(IsNumeric([Person4]),[Person4],[txtZero])

That forces it to use a zero when a person worked no hours so it can sum the row even when some people did no work on that task.

I also set the RunningSum property to "No", which I'd inadvertently set to "Over All" which was causing it to add to the total for the task above it.

 

[SteveR77]

What you have with this as your total field is on the right track but you will need to account for Null vaules-

Add the following function to a Module.

Public Function NullToZero( TheValue as Variant)

'This function converts Null to Zero

'It also converts Non Existing Data to Zero

On Error Goto NullToZero_Err



If ISNull(TheValue) then

NullToZero = 0

Else

NullToZero = TheValue

End if

Exit Function



NullToZero_Err:

'This function would only generate an error

'if the data in TheValue doesn't exist at all.

NullToZero = 0

Exit Function

End Function

Then your Total function will look like this -

Total: NullToZero([Person1]) + NullToZero([Person2]) + NullToZero([Person3]) + NullToZero([Person4])

 

[dhookom]

SteveR77,
Is there a reason why you don't use the built-in Nz() function?
=Nz([Person1],0)+Nz([Person2],0)+...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SteveR77]

Duane,


The Nz function does not exist in all the version of Access that are out there. I'd rather include what I know will work than assume the solution I have given will work.


But you are correct I should have included the option of the Nz function too.

Steve

 

[dhookom]

I think Nz() has been around since at least Access 97. I hope too many people aren't developing in earlier version. Of course, 2.0 was a good version.

The question that hasn't been asked is why values are being summed across fields rather than across records. This sometimes suggests un-normalized table structures.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.