Bring total of listbox column by vba

Check out what values actually are returned:

For i = 0 To SearchBox.ListCount - 1

    Debug.Print i, Val(SearchBox.Column(6, i))

    SearchTotal = SearchTotal + Val(SearchBox.Column(6, i))

Next

Also, as you seem to use column heads, try:

For i = 1 To SearchBox.ListCount

    Debug.Print i, Val(SearchBox.Column(6, i))

    SearchTotal = SearchTotal + Val(SearchBox.Column(6, i))

Next

That was what I expected. The amount is formatted with a thousand separator which is a show-stopper for Val().

Try this, still declaring SearchAmount as Currency:

SearchTotal = SearchTotal + CCur(SearchBox.Column(6, i)))

ciao Irshad,

assiming orders Northwind database example provided by Microsoft :

after creating a form like this, openinig it you will see all datas:

you achieve your task clicking on update command button whose event click code is :

Option Compare Database

Option Explicit

Private Sub cmdUpdate_Click()

With Me

    .txtMessage = Null 

    If Len(.fromDate & vbNullString) = 0 Or Len(.toDate & vbNullString) = 0 Then

        VBA.MsgBox prompt:="date criteria is wrong..!", _

                   Buttons:=vbCritical, _

                   Title:="Warning!!"

        Exit Sub

    End If

    .lstOrders.Requery

    If .lstOrders.ListCount > 0 Then

        .txtMessage = "totRows:" & .lstOrders.ListCount & ", totValue:" & .lstOrders.Column(4, 0)

    End If

End With

End Sub

and 

sql code under listbox control is :

PARAMETERS [forms]![maschera11]![fromDate] DateTime, [forms]![maschera11]![toDate] DateTime;

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.Freight,

            (select sum(Freight) from orders where orderdate>=forms!maschera11!fromDate and orderdate<forms!maschera11!toDate+1 Or [forms]![maschera11]![fromDate] Is Null Or [forms]![maschera11]![toDate] Is Null) AS totFreight

FROM

       Orders

WHERE

     Orders.OrderDate>=[forms]![maschera11]![fromDate] And (Orders.OrderDate)<[forms]![maschera11]![toDate]+1

 OR

   [forms]![maschera11]![fromDate] Is Null OR [forms]![maschera11]![toDate] Is Null;

or like this :

PARAMETERS [forms]![maschera11]![fromDate] DateTime, [forms]![maschera11]![toDate] DateTime;

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.Freight,

              (select sum(Freight) from orders where orderdate>=forms!maschera11!fromDate and orderdate<forms!maschera11!toDate+1 ) AS totFreight

FROM

        Orders

WHERE

       Orders.OrderDate>=[forms]![maschera11]![fromDate] And (Orders.OrderDate)<[forms]![maschera11]![toDate]+1;

maybe the latter suits better your request since you are validating date values range.

in my opionion is an overKill looping through listobox item to sum each one to figure out the total value.

it's convenient add a calculated field on listbox sql predicate and show its content in txtMessage textbox control simply concatenating its value with listCount property.

HTH.

Ciao, Sandro.