Posted on 2015-01-31
i have a query that calculates the Bill Amount based on as you will see in the query.
I built a function GetLarBill in a expression that always is true with the following exception.
If a user selects InsuranceLimit - y (yes/no) box and then enters the Bil lAmount Limit
I would like to the query to execute something like this
if Insurance limit is True
GetLarBill([CustomerID],Nz([billtype]),[LIMIT DOLLAR ABOUT],[TotalSavings])
how would I fit this into the query
SELECT tblRevcodeBill.ClaimID, Sum(tblRevcodeBill.OriginalBill) AS SumOfOriginalBill, Sum(tblRevcodeBill.FeeReduction) AS SumOfFeeReduction, Sum(tblRevcodeBill.AdditionalReduction) AS SumOfAdditionalReduction, Sum(tblRevcodeBill.Settlementoffer) AS SumOfSettlementoffer, Sum(Nz([tblrevcodebill.feereduction])+Nz([tblrevcodebill.additionalreduction])+Nz([tblrevcodebill.Settlementoffer])) AS TotalSavings, Customers.CustomerID, GetLarBill([CustomerID],Nz([billtype]),[sumoforiginalbill],[TotalSavings]) AS LarBill, Sum(Nz([tblrevcodebill.originalbill])-Nz([tblrevcodebill.feereduction])-Nz([tblrevcodebill.additionalreduction])-Nz([tblrevcodebill.settlementoffer])) AS RevisedBill, tblClaim1.BILLTYPE, tblClaim1.INSURANCELIMIT, tblClaim1.[LIMIT DOLLAR ABOUT]
FROM tblSettlem, Customers INNER JOIN (tblRevcodeBill INNER JOIN tblClaim1 ON tblRevcodeBill.ClaimID = tblClaim1.ClaimID) ON Customers.ID = tblClaim1.ID_Cust
GROUP BY tblRevcodeBill.ClaimID, Customers.CustomerID, tblClaim1.BILLTYPE, tblClaim1.INSURANCELIMIT, tblClaim1.[LIMIT DOLLAR ABOUT]
ORDER BY tblRevcodeBill.ClaimID;