VBA Access Invalid Use of Null

user_8742647 0
Viewed 89

Just started with Access VBA today and imagine this is a simple fix. The program calculates total guests in each service category. I must be missing something simple.

Public Sub CalculateTotalGuestsForEachService()

'Declare variables
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intTotalParty As Integer

'Set the current database
Set db = CurrentDb

'Set the recordset
Set rst = db.OpenRecordset("Select Orders.* From Orders Where ServiceID = 1")

'Cycle through the records
Do While Not rst.EOF
    intTotalParty = intTotalParty + rst!NoInParty
rst.MoveNext
Loop

'Display total amount
MsgBox "The total is " & intTotalParty

'Close the recordset
rst.Close
Set rst = Nothing
Set db = Nothing   

End Sub
ms-access vba
1 Answers

If any record has a Null value, apply Nz:

intTotalParty = intTotalParty + Nz(rst!NoInParty.Value, 0)

or, you could let the query sum the values:

'Set the recordset
Set rst = db.OpenRecordset("Select Sum(NoInParty) As TotalParty From Orders Where ServiceID = 1")
If rst.RecordCount = 1 Then
    intTotalParty = Nz(rst!TotalParty.Value)
End If
user_35272970
Related