Calculate List Box Column's Sum (1 Viewer)

[Blake]

I have a text box in which I'd like to display the total of the numbers appearing in the second column of a list box located on the same form as the text box. I've tried a variety of things in the text box's ControlSource, such as:
=Sum([Me].[ListBillingsByTimekeeper].[Column](1))

but I keep getting #error messages. Is there a solution?

Regards, Blake

 

[Pat Hartman]

You can use DSum() to sum the values from a table or query.

 

[Blake]

Thanks very much Pat. For anyone interested, it may help to know that the solution wound up looking at lot different than what I'd been flailing around with (see my initial post). Pat's guidance put me onto this:

=Format(DSum("[myField]","myQuery"),"Currency")

which works, when plunked into the text box's ControlSource. I added the Format/Curreny stuff because the numbers I'm adding are dollar values.

 

[kate10123]

I have a listbox which is based on a query but a user can change the contents of this listbox by clicking a button to perform a different query.

In this instance, I need to change the value of some textboxes which show listbox totals.

How would I use the DSUM method in VB as opposed to straight in the textbox?

so for example txtHours.value = DSUM(NEWQUERYNAME)

 

[wazz]

it's nearly identical to blake's solution:

- buttonclick
- switch queries
- YourForm.txtHours = DSum("YourFieldName", "YourQueryName")

 

[Blake]

Wow--I'd forgotten all about my posting from 2002. But in the interim I've learned (via other contributors to this site) that it can take ages for DSUM to calculate the sum of a list box column. In some cases it took so long that I included an hourglass type symbol to warn the user. It's better to use a function to calculate each column's sum. The function goes through each row one by one and adds up the value for the column and the result can be displayed in a text box. This runs lightning fast compared to DSUM. Here's a code sample for a situation involving column 4 of "ListBoxName":

Function ABill2() As Variant
Dim I As Integer, J As Integer, ctl As Control
Set ctl = Forms![AR Aging (Selected Customer)]![ListBoxName]
J = ctl.listCount - 1

ABill2 = 0
For I = 1 To J      'for each row
    ABill2 = ABill2 + ctl.Column(4, I)  'column 4: totalBilled
Next I

ABill2 = Format(ABill2, "currency")

End Function

 

[wazz]

lol. i didn't notice the original date!
tnx for the update.

 

[Blake]

A better generic solution is provided here: https://access-programmers.co.uk/forums/showthread.php?t=290574