Summing ListBox Column when there is a null value

[papic1972]

Hi All,

This is the function i'm using for summing a listbox column:

Function TonSum() As Variant
Dim I As Integer, J As Integer, ctl As Control
Set ctl = Me.lstDrivera2
J = ctl.ListCount - 1

TonSum = 0
For I = 1 To J
TonSum = TonSum + ctl.Column(8, I)
Next I

End Function

The problem i have is if one the records in the listbox doesn't have a value (is null) then Access gives me a 'Type Mismatch' error. Can anyone suggest how to treat the null value so it excludes it from the sum?

 

[pbaldy]

Have you tried using the Nz() function around the listbox reference?

 

[papic1972]

Hi pbaldy,

yes i did try that after i posted but it still returns the 'Type Mismatch' error. This is what i did:

Function TonSum() As Variant
Dim I As Integer, J As Integer, ctl As Control
Set ctl = Me.lstDrivera2
J = ctl.ListCount - 1

TonSum = 0
For I = 1 To J
TonSum = TonSum + Nz(ctl.Column(8, I), 0)

Next I

End Function

Can you suggest any other alternative other than making the default value of the column zero?

 

[pbaldy]

Are you sure the value is null? Can you post the db here?

 

[papic1972]

Yes it is null. The db frontend is quite large so i can't post it. (How do i paste a screenshot here, i've never done it before?)

 

[pbaldy]

What line exactly errors? Can you post a small sample that recreates the error? Offhand I don't see why that would error.

 

[missinglinq]

The Access Gnomes consider everything in Comboboxes/Listboxes to be Strings! Sometimes they'll allow you to do math-type operations with Strings that look like Numbers, and sometimes they won't.

I got the code to work, placing it behind a Command Button and using a Messagebox to show the results of TonSum, with one tiny hiccup, using these modifications shown in red:

For I = 0 To J
TonSum = TonSum + Val(ctl.Column(1, I))

The Val() converts the String to a Number, to keep the Gnomes happy while doing the addition.

The tiny hiccup was in regards to the Row argument; the Rows are Zero-based. Because of this, using

For I = 1 To J

as in the original code, would skip over the first row in the Listbox. Using '0,' instead, includes the first row.

Linq ;0)>

 

[ChrisO]

Papic1972.

Try to avoid a line of code like:-
TonSum = TonSum + ctl.Column(8, I)
or any other advice you may be given about that line.

TonSum = TonSum + ctl.Column(8, I)
may work to some degree but:-
TonSum = TonSum() + ctl.Column(8, I)
would force the Function to be recursive.

There is very little difference in the syntax between the two lines but the latter would cause an out of stack space error.

Better to create a local variable to hold the running sum and assign that variable to the Function on exit.

Chris.

 

[missinglinq]

Good catch, Chris! As I said, I tested the code off of a Command Button and didn't even notice that TonSum was the name of the Function.

Linq ;0)>

 

[papic1972]

Thank so much missinglinq and ChrisO!! Capturing the first row by changing 1 to 0 makes perfect sense. Thank you!!

 

[missinglinq]

Glad we could help!

Linq ;0)>