VBA: Sum(recordset(field))

[Gregorio]

Access 2000
Win XP Pro
Relevant References: VBA, Access 9.0 Object Library, ActiveX Data Objects
2.6, DAO 3.6

Hi, I am trying the 'sum' operator in code against a field in a recordset
object. The code has done it's job for a year, but if I want to apply a sum
to it I get a compile error 'Sub or Function not defined'.

Code below. Any ideas? Thanks - Greg

Set frm = frmReferrer
With frm
Set ctl = frm.Controls(sCell)

ControlValue = (rst("Total"))
ctl.Value = ControlValue

 

[Graham Mandeno]

Hi Gregorio

Sum is a SQL function, not VBA. You can either open a recordset which
returns the sum, or use the DSum function:

Example of Sum:
Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset( _
"Select Sum([SomeField] from [SomeTable] where <some condition>", _
dbOpenForwardOnly)
ctl.Value = rs(0)
rs.Close

Example of DSum:
ctl.Value = DSum( "SomeField", "SomeTable", "<some condition>" )

 

[Gregorio]

Thanks Graham

I'm doing something a little tricky (for good reasons). The field I am
summing is not a number datatype. Many types odf strings are stored in it
for application architectural reasons, not all need to be summed. So I use
CDbl to convert this into a field on one rst object, then attempt to sum
that field from into a second rst object.

I'll try DSum, haven't used that in a long time!

- Greg

Hi Gregorio

Sum is a SQL function, not VBA. You can either open a recordset which
returns the sum, or use the DSum function:

Example of Sum:
Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset( _
"Select Sum([SomeField] from [SomeTable] where <some condition>", _
dbOpenForwardOnly)
ctl.Value = rs(0)
rs.Close

Example of DSum:
ctl.Value = DSum( "SomeField", "SomeTable", "<some condition>" )

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Access 2000
Win XP Pro
Relevant References: VBA, Access 9.0 Object Library, ActiveX Data Objects
2.6, DAO 3.6

Hi, I am trying the 'sum' operator in code against a field in a
recordset object. The code has done it's job for a year, but if I want to
apply a sum to it I get a compile error 'Sub or Function not defined'.

Code below. Any ideas? Thanks - Greg

Set frm = frmReferrer
With frm
Set ctl = frm.Controls(sCell)

ControlValue = (rst("Total"))
ctl.Value = ControlValue