I have these two vba sql functions that work and I would like to combine the features we have have the Variable sumName, in the first and the <=Date() as shown in the 2nd Function.
Full Function for Select Case for reference.
Code:
sqlString = "Select TBLTRANS.TRNTYP, Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
"From TBLTRANS " & vbCrLf & _
"Group BY TBLTRANS.TRNTYP " & vbCrLf & _
"Having (((TBLTRANS.TRNTYP)='" & sumName & "'));"
Code:
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"");"
Full Function for Select Case for reference.
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 & "'));"
[COLOR=SeaGreen]'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 & _
' "Group BY TBLTRANS.TRNTYP " & vbCrLf & _
' "WHERE TRNACTDTE<=Date() " & vbCrLf & _
' "Having (((TBLTRANS.TRNTYP)='" & sumName & "'));"
[/COLOR] 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 "TBLTRANSStampDuty" 'sql to Sum TBLTRANS.TRNDR for All Records of "Stamp Duty"
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
GetRecordSums = rst!CountOfRec
'Close database variables
rst.Close
dbs.Close
End Function