take a table and an identifying field:
Code:
Function SumFields(tblName As String, _
fldID As Long) As Long
create the objects needed:
Code:
On Error GoTo ErrorHandler
Dim fld As Field
Dim fldSum As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
open your table and find the record that the query is currently looking at:
Code:
Set db = CurrentDb
Set rs = db.OpenRecordset(tblName, dbOpenDynaset)
rs.FindFirst "[ID] = " & fldID
loop the fields and sum the values in that record:
Code:
For Each fld In rs.Fields
If fld.Name <> "ID" Then
fldSum = fldSum + fld
End If
Next fld
SumOfFields = fldSum
cleanup the objects:
Code:
ErrorHandler:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function
in the query's sql, the temp field will read:
Code:
select SumOfFields(thisTable, [ID]
where the identifying field is NAMED ''ID''
and it will output:make more sense?
in your situation, you will loop the fields and check for what you need to. Surely you know how to write short conditionals "if, then" in VBA?