Sum across all Fields 1

[ActMod]

I have a query with 21 Fields. I added a 22nd Field with an expression:=Field1 + Field2 + ... + Field21. When I attempt to view the resulting Query, I get an error message stating "Expression too complex". Does Access have a Field limit with respect to Expressions? Any suggestions as to how I can best add the 21 Fields?
Thank you for any help.
Jim

 

[lespaul]

it will be easier if you post your SQL.

Leslie

 

[hjgoldstein]

I think that you should use Parentheses and Brackets like this:

=([Field1]+[Field2]+[Field3]........+[Field21])

I'm using this syntax on a calculated control (only calculating 18 fields) but it works.

Good Luck.

 

[ActMod]

Thanks to both lespaul and hjgoldstein. In the future, I will post the SQL that I am having problems with. The parenthesis and brackets suggestion was very helpful.
Jim

 

[MichaelRed]

To simplify the statement a small bit, you can use the below function and just set the calculated column to the simple list of fields. If you inadvertantly include a non-numeric field the function will ignore it -but it will include ANY and ALL numeric fields, including "Date", "Currency", etc.

Public Function basRowSum(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/20/2003 (again!)
    'To return the Sum or a series of values

    Dim Idx As Integer
    Dim MyVal As Variant

    For Idx = 0 To UBound(varMyVals())

        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If

        If (IsNumeric(varMyVals(Idx))) Then
            MyVal = MyVal + varMyVals(Idx)
        End If

NextVal:
    Next Idx

    basRowSum = MyVal

End Function
[code]




MichaelRed
m.red@att.net

Searching for employment in all the wrong places