MS Access 2003 - SUM Function

Question

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

Answer 1

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

Answer 2

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.