Totaling the values in a List Box column

Totaling the values in a List Box column

If you wish to get the total for a column in a List Box then you have two options.

Firstly, you can use a custom VBA function, such as this, which loops through the values for the Column and adds them:

Function fListBoxTotal() As Currency
    Dim curValue As Currency
    Dim lngPos As Long
    For lngPos = 0 To ctlListBox.ListCount - 1
        curValue = curValue + ctlListBox.Column(3, lngPos)
    Next lngPos
    fListBoxTotal = curValue
End Function

The above function will return a value that equals the Sum of all data in the fourth Column of a List Box (remember that List Boxes are 0-indexed).

This code can be modified, to return the total of data that has been selected in a List Box as well:

Function fListBoxTotalSelected() As Currency
    Dim curValue As Currency
    Dim varItem As Variant
    For Each varItem In Me!lstOrders.ItemsSelected
        curValue = curValue + Me!lstOrders.Column(3, varItem)
    Next varItem
    fListBoxTotalSelected = curValue
End Function

Secondly, you could use a Domain Aggregate function, DSum, to return the value of data in a column for the List Box. Note that this method won't help if you also want the total for any selected rows in the List Box.

If the query that you have used is called 'qryOrders', and the SQL is:

SELECT Orders.OrderID, Customers.CompanyName, Orders.OrderDate, Sum(([UnitPrice]*[Quantity])) AS OrderTotal
FROM Customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.OrderID, Customers.CompanyName, Orders.OrderDate;

You can add a Text Box to the Form, and set the ControlSource to:

=DSum("[OrderTotal]","[qryOrders]")

You can download an Access 97 database containing a Form showing how this all works from here.

Top

Last modified at 06/06/2006 14:56:54