时间戳

首页 > 解决方案 > VBA访问无效使用Null

vba - VBA访问无效使用Null

问题描述

今天刚开始使用 Access VBA，想象这是一个简单的修复。该程序计算每个服务类别的客人总数。我一定错过了一些简单的东西。

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

标签： vbams-access

解决方案

如果任何记录具有Null值，则应用Nz：

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

或者，您可以让查询对这些值求和：

'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

推荐阅读