Problem with Sum Function

[ryguy7272]

Am trying to figure out a way to sum the values in four columns, basically A,
B, C, D. Each column is represented as TextA, TextB, TextC, and TextD. I
thought I could do something like this: =[TextA]+[TextB]+[TextC]+[TextD]

That method actually works fine, but only is all four columns, on each row,
contain data. If all four (on each row) do not contain data, I just get zero
as a sum. I think I need some kind of conditional sum, but I don’t know how
to do that. Each sum is in the fifth column, and that I am trying to sum
each row at the bottom, in the footer area, as a grand total.

I am fairly now to the wonderful world of Access, so I’m not even sure that
I’m asking the question correctly. Let me know if clarification is needed.

Any help would be greatly appreciated.

Regards,
Ryan--

 

[John Spencer]

You need to force a zero value into the expression if any item is null. You
can do that using the Nz function which converts nulls to zero (or other
specified value). The problem is that a null in an expression will make the
entire expression null.

=Nz([TextA],0) + Nz([TextB],0) + Nz([TextC],0) + Nz([TextD],0)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[ryguy7272]

Whoa! That is pretty clever!! I've been using Excel, at an advanced level,
for quite some time. I am just starting to Learn Access now. I have always
thought of Excel as being the most powerful tool out there, and now I'm
starting to think that Access is a very close runner-up.

Thanks for the help John!!

Regards,
Ryan---

--
RyGuy

You need to force a zero value into the expression if any item is null. You
can do that using the Nz function which converts nulls to zero (or other
specified value). The problem is that a null in an expression will make the
entire expression null.

=Nz([TextA],0) + Nz([TextB],0) + Nz([TextC],0) + Nz([TextD],0)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Am trying to figure out a way to sum the values in four columns, basically A,
B, C, D. Each column is represented as TextA, TextB, TextC, and TextD. I
thought I could do something like this: =[TextA]+[TextB]+[TextC]+[TextD]

That method actually works fine, but only is all four columns, on each row,
contain data. If all four (on each row) do not contain data, I just get zero
as a sum. I think I need some kind of conditional sum, but I don’t know how
to do that. Each sum is in the fifth column, and that I am trying to sum
each row at the bottom, in the footer area, as a grand total.

I am fairly now to the wonderful world of Access, so I’m not even sure that
I’m asking the question correctly. Let me know if clarification is needed.

Any help would be greatly appreciated.

Regards,
Ryan--

 

[ryguy7272]

I have one more question. How do I sum up those sums in one final summary
sum? Does that make sense?

Thanks,
Ryan--

--
RyGuy

Whoa! That is pretty clever!! I've been using Excel, at an advanced level,
for quite some time. I am just starting to Learn Access now. I have always
thought of Excel as being the most powerful tool out there, and now I'm
starting to think that Access is a very close runner-up.

Thanks for the help John!!

Regards,
Ryan---

--
RyGuy

You need to force a zero value into the expression if any item is null. You
can do that using the Nz function which converts nulls to zero (or other
specified value). The problem is that a null in an expression will make the
entire expression null.

=Nz([TextA],0) + Nz([TextB],0) + Nz([TextC],0) + Nz([TextD],0)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Am trying to figure out a way to sum the values in four columns, basically A,
B, C, D. Each column is represented as TextA, TextB, TextC, and TextD. I
thought I could do something like this: =[TextA]+[TextB]+[TextC]+[TextD]

That method actually works fine, but only is all four columns, on each row,
contain data. If all four (on each row) do not contain data, I just get zero
as a sum. I think I need some kind of conditional sum, but I don’t know how
to do that. Each sum is in the fifth column, and that I am trying to sum
each row at the bottom, in the footer area, as a grand total.

I am fairly now to the wonderful world of Access, so I’m not even sure that
I’m asking the question correctly. Let me know if clarification is needed.

Any help would be greatly appreciated.

Regards,
Ryan--

 

[John Spencer]

Add a control to the report's footer (not the page footer)
set its source to
=Sum(Nz([TextA],0) + Nz([TextB],0) + Nz([TextC],0) + Nz([TextD],0))

Or if you are sure that there will be a value in every field in at least
one record
=Sum(TextA) + Sum(TextB) + Sum(TextC) + Sum(TextD)

Access is very powerful and so is Excel. BUT they are good for
different things. Excel is much better at financial analysis, Access is
much better at data manipulation, storage, etc.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[ryguy7272]

Simple yet brilliant!! I guess that's why you are an Access MVP.

Thanks for all the help John. I went with the first function and it worked
brilliantly. The logic of the function is almost exactly the same as the
last one you gave me. I made some small modifications to the other one, but
I just couldn't get it working...maybe I fat-fingered a key or
something...just kept coming up with zeros.

Yes, Excel is much better for financial-related stuff. I got my MBA in
2003. I used Excel quite a bit in school, and I use it all the time now. I
think knowing Excel and Access, both at an advanced level, is a worthwhile
endeavor. Do you think there are a lot of people who are proficient with
both applications or do you think most people use just one or the other?


Anyway, thanks for all the help!
Ryan—



--
RyGuy

Add a control to the report's footer (not the page footer)
set its source to
=Sum(Nz([TextA],0) + Nz([TextB],0) + Nz([TextC],0) + Nz([TextD],0))

Or if you are sure that there will be a value in every field in at least
one record
=Sum(TextA) + Sum(TextB) + Sum(TextC) + Sum(TextD)

Access is very powerful and so is Excel. BUT they are good for
different things. Excel is much better at financial analysis, Access is
much better at data manipulation, storage, etc.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have one more question. How do I sum up those sums in one final summary
sum? Does that make sense?

Thanks,
Ryan--