Using value from table in VB (1 Viewer)

[waxdart23]

I am using the follwing code to count the number of records returned to a list box from a query -

If Not Recset.BOF And Not Recset.EOF Then
Recset.MoveFirst
Recset.MoveLast
NoOfPeriods = .RecordCount
Else
NoOfPeriods = 0
End If

The same query returns a value of days (stored as an integer) to the list box. I would like to find the sum of these days and display them on the form. I have started using the code below but am unsure if this is the correct way to go about it -

If Not Recset.BOF And Not Recset.EOF Then
Recset.MoveFirst
Recset.MoveLast
NoOfDays = NoOfDays + [i]Value of 'tblSickness.TotalDays'[/i]
Else
NoOfDays = 0
End If

I have attached a screen dump of the form to help explain.
Any ideas?

 

[WayneRyan]

Paul,

Dim intLoop As Integer
Dim lngSum As Long

lngSum = 0
For intLoop = 1 to Me.lstBox.ListCount
lngSum = lngSum + Me.lstBox.Column(3)
Next intLoop

hth,
Wayne

 

[waxdart23]

I used the code as below -

    Dim intLoop As Integer
    Dim lngSum As Long
    
    lngSum = 0
    For intLoop = 1 To Me.lstSicknessCount.ListCount
    lngSum = lngSum + Me.lstSicknessCount.Column(3)
    Next intLoop

I got an "Invalid use of Null" error (Run-time error '94'), highlighting the following -
lngSum = lngSum + Me.lstSicknessCount.Column(3)

The data in this column is definately stored as a Long Integer.

Any ideas?

 

[Carmen]

I got an "Invalid use of Null" error (Run-time error '94'), highlighting the following - lngSum = lngSum + Me.lstSicknessCount.Column(3)

Try changing this line to:

lngSum = lngSum + Nz(Me.lstSicknessCount.Column(3), 0)

This will make any null values a 0 and then they will sum.

hth
Carmen

 

[WayneRyan]

Paul,

The .Column(3) was just an example!

I don't know if your listbox is multi-column. If it is, the
subscripts start at 0 for the first column.

Wayne

 

[waxdart23]

Wayne - Thanks, I realised it was an example but it is a coincidence that the column I wish to add is the fourth.

Carmen - Your suggestion stops the error but returns a value of zero.

The fields I am trying to add are required long intergers.

 

[Mile-O]

If you are just trying to get a total, and I'm guessing there's a query that holds the information, why not just use the DSum() aggregate function?

 

[Carmen]

Paul,
Ok Try this...I tested it real quick and it seemed to work.


Dim lngSum As Long
Dim intCurrentRow As Integer

lngSum = 0


For intCurrentRow = 0 To Me.lstSicknessCount.ListCount - 1
lngSum = lngSum + Nz(Me.lstSicknessCount.Column(2, intCurrentRow), 0)
Next intCurrentRow

Me.YourTextBox = lngSum 'to display the sum in a textbox on your form



Carmen

 

[waxdart23]

This is giving a type mismatch now.

Field is formated correctly (see attahced jpeg) and I can't find anywhere else that refers to the field format.

Everything looks OK, but it refuses to work.

It's official... I hate Access.

 

[KevinM]

TIP: DON'T Store 'Days' in your table.

You can calculate this in a query using the DateDiff function with your two Date fields.

In fact for data that is storing sick leave days then it is ESSENTIAL that is calculated and not typed in manually, to minimise error.

 

[Carmen]

You know in my example I used Column 2 when you said you are using Column 3--my typo! Sorry about that....could that be the problem or did you change it already?

 

[Jon K]

Since you have set the Column Heads of the list box to Yes, the row numbers become 1 to ListCount.

---------------------------
Dim lngSum As Long
Dim intCurrentRow As Integer

For intCurrentRow = 1 To Me.lstSicknessCount.ListCount
lngSum = lngSum + Nz(Me.lstSicknessCount.Column(3, intCurrentRow), 0)
Next intCurrentRow

Me.yourTextBox = lngSum 'to display the sum in a textbox on your form
---------------------------

 

[waxdart23]

It now works

Many thanks to everyone who helped on this.

 

[KevinM]

Remember, DON'T store them days in the table

 

[chewy]

waxdart23

I am working on a DB like that. Could you post or email me a copy for me mmajewski@alliancebankna.com

Zipped and in 97 format