Invalid use of null 1

[arsicfilip]

I use this to calculate total sum of column in list box:

Private Sub Command13_Click()
Me!List11.Requery

Dim listrs As Integer
Dim listsum As Integer

listrs = Me![List11].ListCount

Do
listsum = listsum + Me![List11].Column(2, x)
x = x + 1
Loop Until x = listrs
Me![suma].Value = listsum
End Sub

I have problem when query return no record:Invalid use of null.
Is there any way to avoid this?

 

[PHV]

Private Sub Command13_Click()
Dim listsum As Long, x As Integer
With Me!List11
.Requery
For x = 0 To .ListCount - 1
listsum = listsum + Nz(.Column(2, x), 0)
Next
End With
Me!suma.Value = listsum
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[arsicfilip]

It work
THNX a lot

 

[arsicfilip]

Can U explain me how first expression became so short. What is difference' I have tried with Nz function but access has blocked and not respondng.

 

[PHV]

The magic is in the use of a For ... Next loop instead of a Do ... Loop Until.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

I have problem when query return no record"
You mean there's a null data value in the listbox? Something's wrong.
You mean the source of the listbox has no records? Then why are you trying to do a sum?
It seems you found a solution to a problem that shouldn't exist.

 

[arsicfilip]

Mr. Fneily

I used your function to sum column.

List box has been used as query result on form from different tables. At some point query return no result because there is no record to show. There isnt null data there is no record to show based on criteria.

 

[fneily]

Yeah. But your code says:
Private Sub Command13_Click()

So someone must actually click a button. But if the listbox is empty, why would they click it to sum nothing?
As long as you're happy.