How to replace vba Sum Result with Zero if Null

[PNGBill]

Carry on Function from the ones earlier this evening (today)

Still crashes.

RecordID is there, Just no data in the sql result.

Works perfectly when there is data to supply.

Have just tested your code on two options and crashed on one but not the other.

 

[vbaInet]

When you click Debug, what line does it highlight?

 

[PNGBill]

Double checked records and only difference between working and not working is no records in GetMemberPurchasesClubPointsEarned() for the one that crashes.

 

[PNGBill]

When you click Debug, what line does it highlight?

This one

ClubPointsEarned = CurrentDb.OpenRecordset(sqlString).Fields(0)

 

[vbaInet]

Right, this your fix:

    'Assign SQL result to Variable
    ClubPointsEarned = Nz(CurrentDb.OpenRecordset(sqlString).Fields(0), 0)

Go back to your previous code and just amend that line.

 

[PNGBill]

Right, this your fix:

    'Assign SQL result to Variable
    ClubPointsEarned = Nz(CurrentDb.OpenRecordset(sqlString).Fields(0), 0)

Go back to your previous code and just amend that line.

tried that earlier and it failed but just in case I tried again and

Public Function GetMemberPurchasesClubPointsEarned(RecordID As Variant) As Integer
            'RecordID is the Control Value on the Form or Report
            
            Dim sqlString As String
            Dim ClubPointsEarned As Integer
         
                        'Sum Points Traded For MemberID on Report or Form
            sqlString = "SELECT Nz(Sum(tblPointsSoldItems.Points),0) " & vbCrLf & _
                "FROM tblPointsSoldItems " & vbCrLf & _
                "GROUP BY tblPointsSoldItems.MemberID " & vbCrLf & _
                "HAVING (((tblPointsSoldItems.MemberID)=" & RecordID & "));"
                    
         'Assign SQL result to Variable
  [COLOR=Black]  [/COLOR][COLOR=Black]ClubPointsEarned = NZ(CurrentDb.OpenRecordset(sqlString).Fields(0), 0)[/COLOR]
    
    If IsNull(ClubPointsEarned) Then
        'ClubPointsEarned = 0
        Exit Function
    End If
            
            'Assign Variable to Function
    GetMemberPurchasesClubPointsEarned = ClubPointsEarned
     
            
End Function

 

[PNGBill]

Same line highlighted yellow.

What about the record count option ?

I could create a query to replace the table and then have all records with zero's instead of missing data.

 

[vbaInet]

I think this time it's your typo. You forgot to give the Sum() field an alias. Look at our example.

 

[PNGBill]

Sorry, I am confused a little as earlier procedures today worked without this.

How would I do this to also sit with the rest of the code?

 

[PNGBill]

I make some changes but still only works where records exist.

Public Function GetMemberPurchasesClubPointsEarned(RecordID As Variant) As Integer
            'RecordID is the Control Value on the Form or Report
            
            Dim sqlString As String
            Dim ClubPointsEarned As Integer
         
                        'Sum Points Traded For MemberID on Report or Form
            sqlString = "SELECT Nz(Sum(tblPointsSoldItems.Points),0) AS ClubPointsEarned" & vbCrLf & _
                "FROM tblPointsSoldItems " & vbCrLf & _
                "GROUP BY tblPointsSoldItems.MemberID " & vbCrLf & _
                "HAVING (((tblPointsSoldItems.MemberID)=" & RecordID & "));"
                    
         'Assign SQL result to Variable
    ClubPointsEarned = NZ(CurrentDb.OpenRecordset(sqlString).Fields(0), 0)
    
    If IsNull(ClubPointsEarned) Then
        'ClubPointsEarned = 0
        Exit Function
    End If
            
            'Assign Variable to Function
    GetMemberPurchasesClubPointsEarned = ClubPointsEarned
     
            
End Function

 

[vbaInet]

A space after the Alias.

 

[PNGBill]

And this version also only works where records exist

Public Function GetMemberPurchasesClubPointsEarned(RecordID As Variant) As Integer
            'RecordID is the Control Value on the Form or Report
            
            Dim sqlString As String
            Dim ClubPointsEarned As Integer
            Dim PointsEarned As Integer
         
                        'Sum Points Traded For MemberID on Report or Form
            sqlString = "SELECT Nz(Sum(tblPointsSoldItems.Points),0) AS PointsEarned" & vbCrLf & _
                "FROM tblPointsSoldItems " & vbCrLf & _
                "GROUP BY tblPointsSoldItems.MemberID " & vbCrLf & _
                "HAVING (((tblPointsSoldItems.MemberID)=" & RecordID & "));"
                    
         'Assign SQL result to Variable
    ClubPointsEarned = NZ(CurrentDb.OpenRecordset(sqlString).Fields(0), 0)
    
    If IsNull(PointsEarned) Then
        'ClubPointsEarned = 0
        Exit Function
    End If
            
            'Assign Variable to Function
    GetMemberPurchasesClubPointsEarned = ClubPointsEarned
     
            
End Function

 

[PNGBill]

No difference with both of my last two code options. only works where record exists. (after inserting the space)

 

[vbaInet]

You were still missing the space. I noticed you didn't take on board what lagbolt told you about not using vbcrlf.

Try this:

                        'Sum Points Traded For MemberID on Report or Form
            sqlString = "SELECT Sum(tblPointsSoldItems.Points) AS PointsEarned " & _
                "FROM tblPointsSoldItems " & _
                "GROUP BY tblPointsSoldItems.MemberID " & _
                "HAVING (((tblPointsSoldItems.MemberID)=" & RecordID & "));"

 

[PNGBill]

Same story - works for some and not others and same line highlighted

Public Function GetMemberPurchasesClubPointsEarned(RecordID As Variant) As Integer
            'RecordID is the Control Value on the Form or Report
            
            Dim sqlString As String
            Dim ClubPointsEarned As Integer
            Dim PointsEarned As Integer
         
                        'Sum Points Traded For MemberID on Report or Form
            sqlString = "SELECT Sum(tblPointsSoldItems.Points) AS PointsEarned " & _
                "FROM tblPointsSoldItems " & _
                "GROUP BY tblPointsSoldItems.MemberID " & _
                "HAVING (((tblPointsSoldItems.MemberID)=" & RecordID & "));"
                    
         'Assign SQL result to Variable
    ClubPointsEarned = NZ(CurrentDb.OpenRecordset(sqlString).Fields(0), 0)
    
    If IsNull(ClubPointsEarned) Then
        'ClubPointsEarned = 0
        Exit Function
    End If
            
            'Assign Variable to Function
    GetMemberPurchasesClubPointsEarned = ClubPointsEarned
     
            
End Function

 

[vbaInet]

What is the error message?

 

[PNGBill]

No current record.

Run-time-error '-2147352567(80020009)

 

[vbaInet]

Copy and paste:

Public Function GetMemberPurchasesClubPointsEarned(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
         GetMemberPurchasesClubPointsEarned = rst!PointsEarned
    else
         GetMemberPurchasesClubPointsEarned = 0
    end if

    rst.close
    set rst = nothing
            
End Function

Is MemberId a Number datatype by the way?

 

[PNGBill]

Works Great Thanks for your patience vbaInet.

Looks like we do need to count the records sometimes.

tblPointsSoldItems.MemberID is Long Integer - does this make a difference??

 

[vbaInet]

No not really. I was just wondering what datatype it was.

In respect to the recordcount, sometimes a recordset doesn't return a record so it will fail if we try to retrieve a field's value without checking. You can also use

If Not (rst.bof and rst.eof) Then