VBA Access Invalid Use of Null

Question

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

"the record is a zero"? VBA does not treat NULL values as zero, and you cannot add a NULL value to a number, it has to be a number. Check to see if it's NULL before trying to add it. — braX
– braX, Commented Nov 17, 2020 at 7:56
Nulls are not considered, they either are nulls or they aren't. If there is a null, you cannot add that to a number. If you want to treat your nulls as zeroes, use the NZ function. But what you should really do is db.OpenRecordset("Select sum(NoInParty) From Orders Where ServiceID = 1") instead of fetching all those irrelevant columns and rows and slowly adding them one by one on the client. — GSerg
– GSerg, Commented Nov 17, 2020 at 7:58

Gustav · Accepted Answer · 2020-11-17 07:58:43Z

1

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

answered Nov 17, 2020 at 7:58

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FunThomas Over a year ago

or use DSum - no need to deal with a recordset

Collectives™ on Stack Overflow

VBA Access Invalid Use of Null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related