How to replace vba Sum Result with Zero if Null

[PNGBill]

I need some assistance please with how to address the record count in this code:

Public Function GetMemberClubPoints(PointsOption As String, RecordID As Long) As Integer
                'PointsOption is the Select Case name below
                'RecordID is the ID value of the record either ADPK or LDPK as the case may be
        
                'Calculate Club Points 4 for each K100 of Principal X Club Points Factor (1 or 2)
        Const SQL_BASE As String = _
            "SELECT Sum(LDPrin/100*4*ClubPointsFactor) " & _
            "FROM TBLLOAN "
        Dim where As String
             
        Select Case PointsOption
            Case "MemAllLoans"          'Sum All Club Points, earned from Loans, for Selected Club Member - All Loans
                where = "WHERE ADPK = " & RecordID
            Case "MemCurrentLoans"      'Sum Club Points for Selected Member, earend from Loans, Where Loan is Current - not completed
                where = "WHERE ADPK = " & RecordID & " AND LDTerm = 1"
            Case "MemCompletedLoans"    'Sum Club Points for Selected Club Member, earned from Loans, Where Loan is Completed
                where = "WHERE ADPK = " & RecordID & " AND LDTerm = 2"
            Case "LoanPoints"           'Calculate Club Points for Selected Loan
                where = "WHERE LDPK = " & RecordID
            Case Else
                Exit Function       'return value defaults to zero
        End Select
    
                'Assign Result to an Integer and round result to whole number
        GetMemberClubPoints = CurrentDb.OpenRecordset(SQL_BASE & where).Fields(0)
            
    
End Function

I can do it with this code:

But the way the code above is written different and my attempts so far have not worked.

Public Function GetMemPurchClubPoints(RecordID As Variant) As Long
            'RecordID is the Control Value on the Form or Report
            
            Dim rst As DAO.Recordset
            Dim sqlString As String
         
                        'Sum Points Traded For MemberID on Report or Form
            sqlString = "SELECT Sum(Points) AS PointsEarned " & _
                "FROM tblPointsSoldItems " & _
                "GROUP BY MemberID " & _
                "HAVING (((MemberID)=" & RecordID & "));"
    
    Set rst = CurrentDb.OpenRecordset(sqlString)

    If rst.RecordCount <> 0 Then
         'Assign SQL result to Variable
         GetMemPurchClubPoints = rst!PointsEarned
    Else
         GetMemPurchClubPoints = 0
    End If

    rst.Close
    Set rst = Nothing
            
End Function

 

[vbaInet]

You've forgotten to include the name of the alias again and you've gone back to using openrecordset("").fields(0). Both these cases we addressed in this thread.

Adapt the code to the one below. The only thing that should be added is the sql and add the SELECT CASE statement.

 

[PNGBill]

This code was done for me from another thread and works ok until we strike nulls.

So I will just rewrite as per our standard way with Select Case and the test of record count.

thanks vbaInet

 

[vbaInet]

Yeh, that was the problem we had with it the last time which was why we resorted to using the standard way

Happy coding!