CODEWITHSUNDEEP
ms-accesssum
Jeff Anderson
Jeff Anderson

Reputation: 3

MS Access 2003 - SUM Function

How do you calculate with code the SUM of a row of data instead of a column of data?

Upvotes: 0

Views: 1139

Answers (2)

David-W-Fenton
David-W-Fenton

Reputation: 23067

How about this:

  Public Function iSum(ParamArray p()) As Variant
    Dim i As Long
    Dim lngUBound As Long
    Dim v As Variant

    v = Nz(p(LBound(p)), 0)
    lngUBound = UBound(p)
    For i = LBound(p) + 1 To lngUBound
      If Not IsNull(p(i)) Then
         v = v + p(i)
      End If
    Next
    If IsNull(v) Then
       v = 0
    End If
    iSum = v
  End Function

And in SQL:

  SELECT col1, col2, col3, iSum(col1,col2,col3) As Sum 
  FROM Table

You can pass any number of columns, and it doesn't matter if any of them are Null. If all are Null, it returns 0.

Upvotes: 0

Erich
Erich

Reputation: 3972

There is no function for this one, you would have to do something like:

select col1, col2, col3, (nz(col1,0)+nz(col2,0)+nz(col3,0)) as Sum from Table

Upvotes: 2

Related Questions