write forumlas in code

[gwilym40]

Hi I have a continious form for which I'd like to write a formula in code

i.e.
write the select statement and then
say

Dim result as string

result = theforumla

I have 12 fields (Month1 to Month12)

I want to, for the sum of ALL 12 fields (not just Month1)

=Sum(IIf(IsNumeric([Month1]),Val([Month1]),0))

Any ideas?
Thanks

 

[KenReay]

Hi if your columns are called Month1, Month2 etc try somthing like:

Dim curTotal As Currency
Dim i
Dim Rs as DAO.Recordset

Set Rs = Db.OpenREcordset("SELECT ...etc)
or the equivalent ADO contruct if you are using ADO

curTotal = 0
For i = 1 to 12
curTotal = curTotal + Nz(Rs("Month" & i),0)
Next i

Hope this helps

Ken Reay

 

[MichaelRed]

Call the following procedure with the fields you want to sum as the arg list. Per your example:

Result = basSum([Month1], [Month2], ... [Month12])

Although I'm at a loss as to WHY you want to convert the calculation to a string data type ...

Public Function basSum(ParamArray varMyVals()) As Variant

    'Michael Red 3/7/2002
    'To return the SUM of a series of values

    'Sample Usage:
    '? bassum(1, 2.0067, 3)
    '6.0067

    Dim Idx As Integer
    Dim MySum As Variant

    For Idx = 0 To UBound(varMyVals())
        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If
        If (IsNumeric(varMyVals(Idx))) Then
            MySum = MySum + varMyVals(Idx)
        End If
NextVal:
    Next Idx

    basSum = MySum

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over