Can I mix Where and Having in vba sql? (1 Viewer)

[PNGBill]

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.

sqlString = "Select TBLTRANS.TRNTYP, Sum(TBLTRANS.TRNDR) AS CountOfRec " & vbCrLf & _
                    "From TBLTRANS " & vbCrLf & _
                    "Group BY TBLTRANS.TRNTYP " & vbCrLf & _
                    "Having (((TBLTRANS.TRNTYP)='" & sumName & "'));"

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.

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

 

[vbaInet]

Have you tried it out first?

 

[Brianwarnock]

I agree with vbaInet that you could try it, however I believe the answer to your question, without checking to see if it helps you, is yes you can. The Where takes effect before grouping and the Having after.

Brian

 

[PNGBill]

Yes, I did try a few options. See green lettering in the full Select Case above.

get error message about ()'s

I will keep trying in the morning - thanks for the advice as once I get it working I then want to also do the date as a variable.

 

[vbaInet]

The WHERE clause comes before the GROUP BY.

 

[PNGBill]

I got this sql to work in normal access

strSql = "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=""Interest"";"

And I translated it to vba via Allen Browne's form.
Edited the last row to allow for sumName.

vba error message is:
#3075
syntax error in query expression (((TBLTRANS.TRNTYP='Interest'));'.

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 & "'));"

 

[vbaInet]

It has to do with your parentheses in the HAVING part.

You always mention Allen Browne's form, I don't even know which form you mean. I've never heard of it. What does it do?

 

[PNGBill]

It converts access sql to vba sql

here is the link
http://allenbrowne.com/ser-71.html

 

[PNGBill]

Thanks vbaInet - this should have been obvious to me
Must have been tired last light. The parenthesis were my doing.

Here is the working Select Case.

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 & "'));"

 

[vbaInet]

Goodie! Glad it's working. Seems like Allen Browne's tool is very uselful for those wanting to learn SQL syntax. I prefer to do it the hard way but good luck.