How to replace vba Sum Result with Zero if Null

P

PNGBill

Win10 Office Pro 2016
Local time
Tomorrow, 02:48
Joined
Jul 15, 2008
Messages
2,271
This code works, for some variables where data exists but if no data, an error message is returned rather then Zero.

I tried some ways to get an IIf in there but no luck.

Sum(TBLTRANS.TRNDR) AS CountOFRecord needs, if possible, to be reworded to allow for Zero instead of crash when no records exist.

Code: 
Case "TBLTRANS.TRNDRFuture"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated In the Future
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE>Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
 
The sql itself should not care if there are no records so I assume the problem is what comes next. The context is not entirely clear but one of these would be the answer.

Use the HasData property of the recordset.
http://msdn.microsoft.com/en-us/library/aa195920(office.11).aspx

If you are not using a recordset, DCount on the essence of the query first and adjust the procedure to suit.
 
Here is the end of the Select Case.

Code: 
  If Len(sqlString) = 0 Then
        GetRecordSums = 0
        Exit Function
    End If
    
    'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
    
    ' Return the count to the function
    GetRecordSums = rst!CountOfRec
    
    'Close database variables
    rst.Close
    dbs.Close
    
End Function

I was working on using If Then with DCount and can get it to work, where a record exists but not if one doesn't.

Code: 
Case "TBLTRANS.TRNDRFuture"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated In the Future
            If DCount("[TRNTYP]", "TBLTRANS", "[TRNACTDTE]>Date()") >= 1000 Then
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE>Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
            Else
           sqlString = "SELECT 0 AS CountOfRec; "
            End If

The code above works when I use "Interest" as sumName.
In this case there are less then 1000 records and the Function returns 0 (zero) as the If fails and Then takes over.

If I use "Stamp Duty" as sumName the Function crashes because there are no records to count.

DCount appears to be only counting where there is something to count ie it can't return 0.

my access is 2000
 
I added an If Then ahead of sqlString in the hope of catching where no records exit but with no luck.
Either error 3021 or the correct result where records do exist.

Code: 
Case "TBLTRANS.TRNDRFuture"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated In the Future
            If IsNull(DCount("[TRNTYP]", "TBLTRANS", "[TRNTYP]='" & sumName & "'AND [TRNACTDTE]>Date()")) Then
            sqlString = "SELECT 0 AS CountOfRec; "
            Else
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE>Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
            End If

I will keep trying:(
 
Sorry I can't understand what you are trying to do.

However you definitely would not use both IsNull and DCount together like that.
You could use:
If DCount(etc) = 0 Then

But since you are using a recordset it would expect you would use:
If rst.HasData Then
 
Sorry for appearing thick but how can you have a TRNACTDTE>Date() ?:confused:
 
We have records with forward Transaction Dates ie Interest Due in the future.
The records with Date() and prior are an asset and records with >Date() are just required to agree the balance against the Sum of all records.
 
GalaxiomAtHome said:
Sorry I can't understand what you are trying to do.

However you definitely would not use both IsNull and DCount together like that.
You could use:
If DCount(etc) = 0 Then

But since you are using a recordset it would expect you would use:
If rst.HasData Then
Click to expand...
I am trying to Sum the records in TBLLOANS.TRNDR where .TRNTYP = variable and .TRNACTDTE is after a certain date - in this case today but once I get it to work, I may want a variable date.

How would rst.HasData work when it doesn't yet know what the data is. ie if I put it prior to the two sql versions then the record set hasn't yet been defined.

I gues I will have to include it in the sql somehow ??:confused:
 
This works
Code: 
If DCount("[TRNTYP]", "TBLTRANS", "[TRNACTDTE]>Date()") = 0 Then
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE>Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
            Else
                    sqlString = "SELECT 0 AS CountOfRec; "
            End If

Don't know why because it seems to be back to front.

DCount.. If record count = 0 then do full sql option and if not zero, then 0 is the value of CountOfRecord.

But it actually behaves in reverse:confused:
When I use Interest as the variable, there are records, then you would imagine zero is returned because it should move to the 2nd sql yet it returns the value of the records summed.
And if I type "Late fee" Zero is returned ?

It may have something to do with DCount not really checking if the records exist where TRNTYP = sumName as then they would be Zero. But when I tried to include this, the function returned the 3021 error message:confused:
 
The recordset will create quite happily even if there are no records. The sql output is not the issue so you don't need alternative sql or the DCount.

The 3021 error will be thrown from the line:
GetRecordSums = rst!CountOfRec

It should be fine if it is preceded by:

If Not rst.HasData Then
GetRecordSums = 0
GoTo Exit_GetRecordSums
Else

Note the GoTo.
It is a very common bad practice to Exit from the middle of a function or sub
Always redirect the code to the same Exit point.

Here you will close and set the objects to Nothing. Otherwise you would have to do this at every different exit point.

In your Function they will not be cleaned up if the following line returns true.
If Len(sqlString) = 0

It is especially important to those who follow you or you next year when you make some changes. Then you might add some more objects and forget that you can jump out the window instead of leaving by the door, so to speak.
 
Thanks GalaxiomAtHome.

Here is the full procedure.

I added what I believe is the correct error handling and exit lines.

Error - Compile Error:
Method or data member not found
.HasData is highlighted from the line
If Not rst.HasData Then

My version is 2000




Code: 
Public Function GetRecordSums(sumID As String, Optional sumName As String) As Currency
On Error GoTo Err_GetRecordSums

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    
    Select Case sumID
        Case "TBLTRANS.TRNDR"  'Sum Selected Records in TBLTRANS.TRNDR "Interest", "Late fee", Legal Fees", "Stamp Duty",
                                    ' "Principal", "Fee Aplic" & "Fee Process"
            sqlString = "Select TBLTRANS.TRNTYP, Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "From TBLTRANS " & vbCrLf & _
                    "Group BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "Having (((TBLTRANS.TRNTYP)='" & sumName & "'));"
        Case "TBLTRANS.TRNDRToDate"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated Today or Prior to Today
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE<=Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
        Case "TBLTRANS.TRNPR"  'Sum All Records in TBLTRANS.TRNPR
            sqlString = "SELECT Sum(TBLTRANS.TRNPR) AS CountOFRec FROM TBLTRANS;"
        Case "InterestToDate"   'sql query to Sum TBLTRANS.TRNDR for Interest to and including today's date
            sqlString = "SELECT Sum(TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TRNACTDTE<=Date() AND TRNTYP = (""Interest"");"
        Case "InterestFuture"       'sql to Sum TBLTRANS.TRNDR for Interest After Today's Date
            sqlString = "SELECT Sum(TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TRNACTDTE>Date() AND TRNTYP = (""Interest"");"
        Case "TBLTRANS.TRNDRAll"  'Sum All Records in TBLTRANS.TRNDR
            sqlString = "SELECT Sum(TBLTRANS.TRNDR) AS CountOfRec FROM TBLTRANS;"
        Case "TBLTRANS.TRNDRFuture"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated In the Future
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE>Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
        Case "TBLLOANCount"     'Count all records in TBLLOAN
            sqlString = "Select TBLLOAN.LDTerm, Count(TBLLOAN.LDPK) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.LDPRIN"       'Sum Selected Records in TBLLOAN.LDPRIN where LDTerm = 1 (Current), 2 (Completed) or 3 (Cancelled)
            sqlString = "Select TBLLOAN.LDTerm, Sum(TBLLOAN.LDPRIN) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.LDAFee"       'Sum Selected Records in TBLLOAN.LDAFee where LDTerm = 1 (Current), 2 (Completed) or 3 (Cancelled)
            sqlString = "Select TBLLOAN.LDTerm, Sum(TBLLOAN.LDAFee) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.LDPFee"       'Sum Selected Records in TBLLOAN.LDPFee where LDTerm = 1 (Current), 2 (Completed) or 3 (Cancelled)
            sqlString = "Select TBLLOAN.LDTerm, Sum(TBLLOAN.LDPFee) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.StampDuty"       'Sum All Records in TBLLOAN.StampDuty
            sqlString = "Select Sum(TBLLOAN.StampDuty) AS CountOfRec From TBLLOAN;"
        Case "RepaymentsAll"        'Sum Member Repayments in tblMemberRepayments.PaymentsAmt
            sqlString = "SELECT Sum(tblMemberRepayments.PaymentAmt) AS CountOFRec FROM tblMemberRepayments;"
        Case "EmployerCount"        'Count Employers in TBLEMPDET.EDPK where EDPayroll = 1 (Payroll) and 2 (non Payroll)
            sqlString = "Select TBLEMPDET.EDPayroll, Count(TBLEMPDET.EDPK) AS CountOfRec " & vbCrLf & _
                    "From TBLEMPDET " & vbCrLf & _
                    "Group BY TBLEMPDET.EDPayroll " & vbCrLf & _
                    "Having (((TBLEMPDET.EDPayroll)=" & sumName & "));"
        Case "MemberCountAll"       'Count All Members in TBLACCDET.ADPK
             sqlString = "SELECT Count(TBLACCDET.ADPK) AS CountOfRec FROM TBLACCDET;"
        Case "MemberPastCount"      'Count Member Number in TBLACCDET.ADPK where CurrentMember = 0 (Past Member) and -1 (Current Member)
            sqlString = "Select TBLACCDET.CurrentMember, Count(TBLACCDET.ADPK) AS CountOfRec " & vbCrLf & _
                    "From TBLACCDET " & vbCrLf & _
                    "Group BY TBLACCDET.CurrentMember " & vbCrLf & _
                    "Having (((TBLACCDET.CurrentMember)=" & sumName & "));"
        Case "MemberResignedCount"      'Count Member Number in TBLACCDET.ADPK where EmpFinished = 0 (Resigned) and -1 (Not Resigned)
            sqlString = "Select TBLACCDET.EmpFinished, Count(TBLACCDET.ADPK) AS CountOfRec " & vbCrLf & _
                    "From TBLACCDET " & vbCrLf & _
                    "Group BY TBLACCDET.EmpFinished " & vbCrLf & _
                    "Having (((TBLACCDET.EmpFinished)=" & sumName & "));"
        Case Else
            sqlString = ""
    End Select
    
     If Len(sqlString) = 0 Then
       GetRecordSums = 0
       GoTo Exit_GetRecordSums
     End If
    
    'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
       
    ' Return the count to the function
    If Not rst.HasData Then
    GetRecordSums = 0
    GoTo Exit_GetRecordSums
    Else
    GetRecordSums = rst!CountOfRec
    
    'Close database variables
    rst.Close
    dbs.Close
    
    

Exit_GetRecordSums
    Exit Sub

Err_GetRecordSums
    MsgBox Err.Description
    Resume Exit_GetRecordSums
 
End Sub
 
I notice vba doesn't show Hasdata in the option list when I type rst.

RecordCount is showing so I tried:

Code: 
If rst.RecordCount = 0 Then
    GetRecordSums = 0
    GoTo Exit_GetRecordSums
    Else
    GetRecordSums = rst!CountOfRec

and no more error there but now the error Has moved to
Code: 
Exit_GetRecordSums
    Exit Sub
with Exit_GetRecordSums highlighted and the message is "Compile Error:
Sub or Function not defined:confused:
And the first line is Yellow
Code: 
Public Function GetRecordSums(sumID As String, Optional sumName As String) As Currency
 
I have changed the error words End Sub to End Function - copy and past when I bought in the error handling part but no difference to the messages.
 
Back to this code I see :)

It's either you

* Trap for Null values when you're calling the function
Code: 
=GetRecordSums(Nz([ID], 0), Nz([Field2], "")) As Currency

OR

* If you don't want to process Null values, change the parameters to Variants, then check for Null within the function. Checking for Null would be the very first thing you do:
Code: 
Public Function GetRecordSums(sumID As [COLOR=Blue][B]Variant[/B][/COLOR], Optional sumName As [COLOR=Blue][B]Variant[/B][/COLOR]) As Currency

The check:
Code: 
If IsNull(sumID) then
    exit function
end if
 
This appears to work.

A lot of code but really just a long list of Select Case options.

Nulls in the records are not an issue, so far but the issue was no records in the data being evaluated.

The rst.RecordCount at the bottom appears to be resolving this.

Code: 
Public Function GetRecordSums(sumID As String, Optional sumName As String) As Currency

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    
    Select Case sumID
        Case "TBLTRANS.TRNDR"  'Sum Selected Records in TBLTRANS.TRNDR "Interest", "Late fee", Legal Fees", "Stamp Duty",
                                    ' "Principal", "Fee Aplic" & "Fee Process"
            sqlString = "Select TBLTRANS.TRNTYP, Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "From TBLTRANS " & vbCrLf & _
                    "Group BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "Having (((TBLTRANS.TRNTYP)='" & sumName & "'));"
        Case "TBLTRANS.TRNDRToDate"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated Today or Prior to Today
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE<=Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
        Case "TBLTRANS.TRNPR"  'Sum All Records in TBLTRANS.TRNPR
            sqlString = "SELECT Sum(TBLTRANS.TRNPR) AS CountOFRec FROM TBLTRANS;"
        Case "InterestToDate"   'sql query to Sum TBLTRANS.TRNDR for Interest to and including today's date
            sqlString = "SELECT Sum(TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TRNACTDTE<=Date() AND TRNTYP = (""Interest"");"
        Case "InterestFuture"       'sql to Sum TBLTRANS.TRNDR for Interest After Today's Date
            sqlString = "SELECT Sum(TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TRNACTDTE>Date() AND TRNTYP = (""Interest"");"
        Case "TBLTRANS.TRNDRAll"  'Sum All Records in TBLTRANS.TRNDR
            sqlString = "SELECT Sum(TBLTRANS.TRNDR) AS CountOfRec FROM TBLTRANS;"
        Case "TBLTRANS.TRNDRFuture"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated In the Future
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE>Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
        Case "TBLLOANCount"     'Count all records in TBLLOAN
            sqlString = "Select TBLLOAN.LDTerm, Count(TBLLOAN.LDPK) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.LDPRIN"       'Sum Selected Records in TBLLOAN.LDPRIN where LDTerm = 1 (Current), 2 (Completed) or 3 (Cancelled)
            sqlString = "Select TBLLOAN.LDTerm, Sum(TBLLOAN.LDPRIN) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.LDAFee"       'Sum Selected Records in TBLLOAN.LDAFee where LDTerm = 1 (Current), 2 (Completed) or 3 (Cancelled)
            sqlString = "Select TBLLOAN.LDTerm, Sum(TBLLOAN.LDAFee) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.LDPFee"       'Sum Selected Records in TBLLOAN.LDPFee where LDTerm = 1 (Current), 2 (Completed) or 3 (Cancelled)
            sqlString = "Select TBLLOAN.LDTerm, Sum(TBLLOAN.LDPFee) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.StampDuty"       'Sum All Records in TBLLOAN.StampDuty
            sqlString = "Select Sum(TBLLOAN.StampDuty) AS CountOfRec From TBLLOAN;"
        Case "RepaymentsAll"        'Sum Member Repayments in tblMemberRepayments.PaymentsAmt
            sqlString = "SELECT Sum(tblMemberRepayments.PaymentAmt) AS CountOFRec FROM tblMemberRepayments;"
        Case "EmployerCount"        'Count Employers in TBLEMPDET.EDPK where EDPayroll = 1 (Payroll) and 2 (non Payroll)
            sqlString = "Select TBLEMPDET.EDPayroll, Count(TBLEMPDET.EDPK) AS CountOfRec " & vbCrLf & _
                    "From TBLEMPDET " & vbCrLf & _
                    "Group BY TBLEMPDET.EDPayroll " & vbCrLf & _
                    "Having (((TBLEMPDET.EDPayroll)=" & sumName & "));"
        Case "MemberCountAll"       'Count All Members in TBLACCDET.ADPK
             sqlString = "SELECT Count(TBLACCDET.ADPK) AS CountOfRec FROM TBLACCDET;"
        Case "MemberPastCount"      'Count Member Number in TBLACCDET.ADPK where CurrentMember = 0 (Past Member) and -1 (Current Member)
            sqlString = "Select TBLACCDET.CurrentMember, Count(TBLACCDET.ADPK) AS CountOfRec " & vbCrLf & _
                    "From TBLACCDET " & vbCrLf & _
                    "Group BY TBLACCDET.CurrentMember " & vbCrLf & _
                    "Having (((TBLACCDET.CurrentMember)=" & sumName & "));"
        Case "MemberResignedCount"      'Count Member Number in TBLACCDET.ADPK where EmpFinished = 0 (Resigned) and -1 (Not Resigned)
            sqlString = "Select TBLACCDET.EmpFinished, Count(TBLACCDET.ADPK) AS CountOfRec " & vbCrLf & _
                    "From TBLACCDET " & vbCrLf & _
                    "Group BY TBLACCDET.EmpFinished " & vbCrLf & _
                    "Having (((TBLACCDET.EmpFinished)=" & sumName & "));"
        Case Else
            sqlString = ""
    End Select
    
     If Len(sqlString) = 0 Then
       GetRecordSums = 0
       Exit Function
     End If
    
    'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
       
    ' Return the count to the function
    If rst.RecordCount = 0 Then
    GetRecordSums = 0
    Exit Function
    Else
    GetRecordSums = rst!CountOfRec
    End If
    
    'Close database variables
    rst.Close
    dbs.Close
    
  
End Function
 
Here is the full procedure.

I manged to get Case "TBLTRANS.TRNDRFuture working but have now added

Case "RepaymentsFuture" and I am back with the Null issue.

The sql's appear to only have INNER join as a diff.
"RepaymentsFuture" works if I enter Data dated in the future but if no data Error '94' Invalid use of Null appears.

Sorry, vbaInet, but I couldn't decipher your suggestions on Trapping Null.

how would I type in the suggested code to suit the wording we have?

Code: 
Public Function GetRecordSums(sumID As String, Optional sumName As String) As Currency

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    
    Select Case sumID
        Case "TBLTRANS.TRNDR"  'Sum Selected Records in TBLTRANS.TRNDR "Interest", "Late fee", Legal Fees", "Stamp Duty",
                                    ' "Principal", "Fee Aplic" & "Fee Process"
            sqlString = "Select TBLTRANS.TRNTYP, Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "From TBLTRANS " & vbCrLf & _
                    "Group BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "Having (((TBLTRANS.TRNTYP)='" & sumName & "'));"
        Case "TBLTRANS.TRNDRToDate"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated Today or Prior to Today
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE<=Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
        Case "TBLTRANS.TRNPR"  'Sum All Records in TBLTRANS.TRNPR
            sqlString = "SELECT Sum(TBLTRANS.TRNPR) AS CountOFRec FROM TBLTRANS;"
        Case "TBLTRANS.TRNDRAll"  'Sum All Records in TBLTRANS.TRNDR
            sqlString = "SELECT Sum(TBLTRANS.TRNDR) AS CountOfRec FROM TBLTRANS;"
        Case "TBLTRANS.TRNDRFuture"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated In the Future
            sqlString = "SELECT TBLTRANS.TRNTYP,  Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "FROM TBLTRANS " & vbCrLf & _
                    "WHERE TBLTRANS.TRNACTDTE>Date() " & vbCrLf & _
                    "GROUP BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "HAVING (((TBLTRANS.TRNTYP)='" & sumName & "'));"
        Case "TBLLOANCount"     'Count all records in TBLLOAN
            sqlString = "Select TBLLOAN.LDTerm, Count(TBLLOAN.LDPK) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.LDPRIN"       'Sum Selected Records in TBLLOAN.LDPRIN where LDTerm = 1 (Current), 2 (Completed) or 3 (Cancelled)
            sqlString = "Select TBLLOAN.LDTerm, Sum(TBLLOAN.LDPRIN) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.LDAFee"       'Sum Selected Records in TBLLOAN.LDAFee where LDTerm = 1 (Current), 2 (Completed) or 3 (Cancelled)
            sqlString = "Select TBLLOAN.LDTerm, Sum(TBLLOAN.LDAFee) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.LDPFee"       'Sum Selected Records in TBLLOAN.LDPFee where LDTerm = 1 (Current), 2 (Completed) or 3 (Cancelled)
            sqlString = "Select TBLLOAN.LDTerm, Sum(TBLLOAN.LDPFee) AS CountOfRec " & vbCrLf & _
                    "From TBLLOAN " & vbCrLf & _
                    "Group BY TBLLOAN.LDTerm " & vbCrLf & _
                    "Having (((TBLLOAN.LDTerm)=" & sumName & "));"
        Case "TBLLOAN.StampDuty"       'Sum All Records in TBLLOAN.StampDuty
            sqlString = "Select Sum(TBLLOAN.StampDuty) AS CountOfRec From TBLLOAN;"
        Case "RepaymentsAll"        'Sum Member Repayments in tblMemberRepayments.PaymentsAmt
            sqlString = "SELECT Sum(tblMemberRepayments.PaymentAmt) AS CountOFRec FROM tblMemberRepayments;"
             Case "TBLTRANS.TRNDRFuture"  'Sum Selected Records in TBLTRANS.TRNDR 'Interst", "Late fee", "Legal Fees", "Stamp Duty", '
                                    ' "Principal", "Fee Aplic", & "Fee Process" Dated In the Future
        Case "RepaymentsFuture"        'Sum Member Repayments in tblMemberRepayments.PaymentsAmt where tblBankStatement.StatementDate is in the future.
             sqlString = "SELECT Sum(tblMemberRepayments.PaymentAmt) AS CountOFRec " & vbCrLf & _
                    "FROM tblBankStatements INNER JOIN tblMemberRepayments ON tblBankStatements.StatementID = tblMemberRepayments.StatementID " & vbCrLf & _
                    "WHERE tblBankStatements.StatementDate<Date();"
        Case "EmployerCount"        'Count Employers in TBLEMPDET.EDPK where EDPayroll = 1 (Payroll) and 2 (non Payroll)
            sqlString = "Select TBLEMPDET.EDPayroll, Count(TBLEMPDET.EDPK) AS CountOfRec " & vbCrLf & _
                    "From TBLEMPDET " & vbCrLf & _
                    "Group BY TBLEMPDET.EDPayroll " & vbCrLf & _
                    "Having (((TBLEMPDET.EDPayroll)=" & sumName & "));"
        Case "MemberCountAll"       'Count All Members in TBLACCDET.ADPK
             sqlString = "SELECT Count(TBLACCDET.ADPK) AS CountOfRec FROM TBLACCDET;"
        Case "MemberPastCount"      'Count Member Number in TBLACCDET.ADPK where CurrentMember = 0 (Past Member) and -1 (Current Member)
            sqlString = "Select TBLACCDET.CurrentMember, Count(TBLACCDET.ADPK) AS CountOfRec " & vbCrLf & _
                    "From TBLACCDET " & vbCrLf & _
                    "Group BY TBLACCDET.CurrentMember " & vbCrLf & _
                    "Having (((TBLACCDET.CurrentMember)=" & sumName & "));"
        Case "MemberResignedCount"      'Count Member Number in TBLACCDET.ADPK where EmpFinished = 0 (Resigned) and -1 (Not Resigned)
            sqlString = "Select TBLACCDET.EmpFinished, Count(TBLACCDET.ADPK) AS CountOfRec " & vbCrLf & _
                    "From TBLACCDET " & vbCrLf & _
                    "Group BY TBLACCDET.EmpFinished " & vbCrLf & _
                    "Having (((TBLACCDET.EmpFinished)=" & sumName & "));"
        Case Else
            sqlString = ""
    End Select
    
     If Len(sqlString) = 0 Then
       GetRecordSums = 0
       'GoTo FinishProcedure
       Exit Function
     End If
    
    'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
       
    ' Return the count to the function
    If rst.RecordCount = 0 Then
    GetRecordSums = 0
    Exit Function
    'GoTo FinishProcedure
    Else
    GetRecordSums = rst!CountOfRec
    End If
    
'FinishProcedure:
    
    'Close database variables
    rst.Close
    dbs.Close
    Exit Function
    
  
End Function
 
This option appears to work:)

Code: 
Case "RepaymentsFuture"        'Sum Member Repayments in tblMemberRepayments.PaymentsAmt where tblBankStatement.StatementDate is in the future.
             sqlString = "SELECT Nz(Sum([tblMemberRepayments].[PaymentAmt]),0) AS CountOFRec " & vbCrLf & _
                    "FROM tblBankStatements INNER JOIN tblMemberRepayments ON tblBankStatements.StatementID = tblMemberRepayments.StatementID " & vbCrLf & _
                    "WHERE (((tblBankStatements.StatementDate)>Date()));"
 
You will still have problems with Null values.

The Null value problem isn't within the records but in the call to the function. A variable declared as String cannot accept Null (for example) and because you cannot anticipate when a Null value will be sent to the function (when calling it) you have to either trap it. In the function the two parameters or arguments are declared as String so if a Null ID or a Null sumName is sent, it will fail. It has nothing to do with the Sum() function in the SQL as Sum() or even DSum() will ignore null values.

I gave you two ways of trapping for Null. Try the first suggestion then move on to the next. Trap null using Nz() in the call to the function. If you aren't sure what Nz() does then here:

http://www.techonthenet.com/access/functions/advanced/nz.php
 
vbaInet said:
You will still have problems with Null values.

The Null value problem isn't within the records but in the call to the function. A variable declared as String cannot accept Null (for example) and because you cannot anticipate when a Null value will be sent to the function (when calling it) you have to either trap it. In the function the two parameters or arguments are declared as String so if a Null ID or a Null sumName is sent, it will fail. It has nothing to do with the Sum() function in the SQL as Sum() or even DSum() will ignore null values.

I gave you two ways of trapping for Null. Try the first suggestion then move on to the next. Trap null using Nz() in the call to the function. If you aren't sure what Nz() does then here:

http://www.techonthenet.com/access/functions/advanced/nz.php
Click to expand...

I tried all sorts of combinations and can not get the Null issue out of this procedure.

It works for records where there is data but crashes where there is no records.:confused:

I used =GetMemberPurchasesClubPointsEarned(Nz([ADPK],0)) in the data source of the control but still crashes.

Code: 
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
    ClubPointsEarned = CurrentDb.OpenRecordset(sqlString).Fields(0)
    
    If IsNull(ClubPointsEarned) Then
        'ClubPointsEarned = 0
        Exit Function
    End If
            
            'Assign Variable to Function
    GetMemberPurchasesClubPointsEarned = ClubPointsEarned
     
            
End Function
 
I see no reason why it would crash if you're trapping the Null's in the call to the function. Try this instead:
Code: 
Public Function GetMemberPurchasesClubPointsEarned(RecordID As Variant) As Long
'RecordID is the Control Value on the Form or Report
            
     Dim sqlString As String    
     Dim ClubPointsEarned As Integer
     
    If IsNull(RecordID) Then
        Exit Function
    End if

    '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
    ClubPointsEarned = CurrentDb.OpenRecordset(sqlString).Fields(0)
    
    If IsNull(ClubPointsEarned) Then
        'ClubPointsEarned = 0
        Exit Function
    End If
            
    'Assign Variable to Function
    GetMemberPurchasesClubPointsEarned = ClubPointsEarned
     
            
End Function
Copy and paste.

By the way, this seems to be a different function?
 
You must log in or register to reply here.

Similar threads

T
How to return zero in a field if it is null
Replies
4
Views
808
arnelgp
arnelgp
P
Can I mix Where and Having in vba sql?
Replies
9
Views
4,986
vbaInet
V
P
Is this the acceptable way to do a Function Procedure?
Replies
9
Views
1,274
PNGBill
P
P
Date Variable not working in vba code
Replies
6
Views
9,717
pbaldy
pbaldy
P
Can I do an If Then Else in sql??
Replies
4
Views
1,264
DCrake
DCrake

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom