Code:
Dim loRS as Object
SELECT R1.AuditID, Left(NZ(R1.TranCode,""), 3) AS ClientCode,
Max(R1.TranCodeText) AS MaxOfTranCodeText,
Sum(IIF(UCase(NZ(R1.User019_Deducted,"")) IN ("-1","TRUE"), R1.NetAmt + abs(R1.[PaybackAmt]), 0 )) AS GrossDeducted,
Sum(IIF(UCase(NZ(R1.User019_Deducted,"")) IN ("-1","TRUE"), R1.PaybackAmt, 0 )) AS PayBacks,
Sum(IIF(UCase(NZ(R1.User019_Deducted,"")) IN ("-1","TRUE"), R1.[NetAmt], 0 )) AS NetDeducted,
Sum(IIF(UCase(NZ(R1.User019_Deducted,"")) NOT IN ("-1" ,"TRUE")
AND UCase(NZ(R1.User004_Uploaded,"")) IN ("-1","TRUE"), R1.[NetAmt], 0
)
) AS Uploaded,
Sum(IIF(UCase(NZ(R1.User003_MailDate,"")) NOT IN ("")
AND UCase(NZ(R1.User004_Deducted,"")) IN ("","0","FALSE")
AND UCase(NZ(R1.User019_Deducted,"")) IN ("","0","FALSE") , R1.NetAmt, 0
)
) AS Mailed,
SUM(IIF(ISNULL(R1.AuditId)= True, 1, 0 ) ) AS Cnt
FROM (RptCPClaimsRollupRACReportDetail R1
Left Join RptCPClaimsRollupRACReport_ClaimCodeDesc R2
ON R1.ClientCode = R2.ClaimCode)
Left Join ScrCpClaimsData SCD
ON R1.AuditID = SCD.AuditID AND R1.ClaimNum = SCD.ClaimNum
WHERE R1.AuditID IN( 6133,6134,6183 )
AND ( IIF(ISNULL(R1.user003_MailDate)= True, Cdate("01/01/1900"), Cdate(R1.user003_MailDate)) < CDate("01/21/2022")
AND IIF(ISNULL(R1.LastTranDate)= True, Cdate("01/01/1900"), R1.LastTranDate) < CDate("01/21/2022")
AND IIF(ISNULL(SCD.UploadDate)= True, Cdate("01/01/1900"), SCD.UploadDate) < CDate("01/21/2022")
AND IIF(ISNULL(SCD.LastDedDate)= True, Cdate("01/01/1900"), SCD.LastDedDate) < CDate("01/21/2022")
)
GROUP BY R1.AuditID, Left(NZ(R1.TranCode,""), 3)
Set loRS = CurrentDb.OpenRecordset(xSqlMain, dbReadOnly, dbReadOnly)