Total a column in a listbox

  • Thread starter Thread starter Barry Erick
  • Start date Start date
B

Barry Erick

I am trying to total a column in a list box. Nothing needs to be selected,
selected is ignored. It is total of listcount-1 and has a header. The data
is a filtered or not filtered item from one database and the query changes
for the filter (looks at dates and expired status).
How to retrieve the value from this column and sum them all up. This is not
a running sum to go into another column. I show it at the bottom of the form
similar to a report has a total at the bottom of a page. I did it before but
my computer crashed on the weekend and my backup was bad.
 
I am trying to total a column in a list box. Nothing needs to be selected,
selected is ignored. It is total of listcount-1 and has a header. The data
is a filtered or not filtered item from one database and the query changes
for the filter (looks at dates and expired status).
How to retrieve the value from this column and sum them all up. This is not
a running sum to go into another column. I show it at the bottom of the form
similar to a report has a total at the bottom of a page. I did it before but
my computer crashed on the weekend and my backup was bad.
Click to expand...

A Listbox is just a display tool, not a data storage medium. I'd
suggest using the DSum() function to sum up the values from the Table
or Query upon which the listbox is based.
 
Well Q165501 does show there is a way to get information from a multi column
list box, but it is adding the columns together and sending the result to a
label. I want to start at the first row, get the value, go to the next row
and do the same until I reach listcount-1.

I know I added this very simply (for a real light Access programmer, but a
very knowledgable programmer in other languages) but just don't see the
tecnique. So far, DSum in the vba routine that updates the listbox with a
simple with me![ctrlList] then .rowsource = SQLText and a .requery and
END WITH fills this. The DSUM next calls the db and uses the part of SQLText
following the Where for Criteria, but I get syntax error... The database is
called bills. The table is bills. The field is PaymentDue (Currency Type)
and the SQLText is the same as following the where.
 
I finally found my backup and FYI: This is the VBA code I used to read the
values from the 4th column and get a total into a lable below the listbox:

Function fListBoxTotal() As String
Dim CurVal As Currency
Dim LngPos As Long
Dim Tot As String
For LngPos = 1 To CtrlList.ListCount - 1
CurVal = CurVal + CtrlList.Column(3, LngPos)'columns are 0 based
Next
Tot = Format(CurVal, "#$##,##0,00") 'could dim as currency and make the
function that, too.
fListBoxTotal = Tot

End Function
 
I finally found my backup and FYI: This is the VBA code I used to read the
values from the 4th column and get a total into a lable below the listbox:

Function fListBoxTotal() As String
Dim CurVal As Currency
Dim LngPos As Long
Dim Tot As String
For LngPos = 1 To CtrlList.ListCount - 1
CurVal = CurVal + CtrlList.Column(3, LngPos)'columns are 0 based
Next
Tot = Format(CurVal, "#$##,##0,00") 'could dim as currency and make the
function that, too.
fListBoxTotal = Tot
Click to expand...

Ok, this code looks reasonable (though I'd still suggest that using
DSum() to sum the values in a query will be more efficient). What is
it doing or not doing that you want? What's the Rowsource of the
listbox?
 
Back
Top