Unable to use the Sum function in access visual basic

Question

Have a vb program which i am trying to use the Sum function but am unable. Compiler says not defined, Think there is a reference i need to a library. Any one got a idea? TIA

Answer 1

Sum is not a VBA function. It is an aggregate function that you can use in queries and in SQL statements. And it is a worksheet function in Excel.

Can you provide some details about how you want to use Sum in Access VBA?

Answer 2

Maybe you really wanted to use the DSum function. Check it out in the VBA help file.

Answer 3

Maybe u can help me understand the dsum method:

I have table "attendance" which has fields of "d1", "d2", ...

I am trying to sum all the entries in "d1" which can be 70 or more

Some of the entries in "d1" are blanks (never had any thing entered or a text number)

here is part of the program:

    Dim Nsum As Integer, Isum As Integer, Csum As Integer, Iattendance As Integer

    Dim rstAttend As Recordset

    With rstAttend

    Set rstAttend = dbsDatabase.OpenRecordset("attendance", dbOpenDynaset)

    Nsum = 0

    Isum = 0

    For I = 1 To 36

    strTemp = "d" & I

    Csum = DSum(rstAttend.Fields("d" & I), "attendance")

When i execute the pgm i get: improper use of null or some thing like that, any idea what i am doing wrong? TIA

Answer 4

d1, d2, ... d36: that sounds like a likely violation of relational database design rules. Can you please post a Relationships diagram of the most important tables? I'm assuming you are interested in doing things the way Access (and every other RDBMS) was designed to be used.

Answer 5

I think u have me there. Have no idea what a relational db is. 

here is what a design view of what table attendance looks like:

attendance (table name)

fields:

roster name d1 d2 d3 d4 ...

eg:

1         sue    1         1        

2         ed            1

3         sam                   1

sue attended on day1 & day3

ed   attended on day2

sam attended on day4

...

class days (table name)

fields:

startday classday1 classday2 classday3 ...

students(table name) 

fields are about info of the student name, address ...

BTW I thought dsum would save me writing a for loop but i am over my head. Started with fortran do loops in 1956 & hard to teach an old dog new tricks :-)