How to omit Zero results when performing Query

[Whitemtntn]

MS Access Query- wrote an expression with Build.
It's a simple Sum of other fields in the query. The other fields also get calculated in the query, they are not "static" numbers. It works fine.
But.. in the performed Query we want to omit/suppress all results of zero from the expression.
Tried a few things, including using <> 0 in the criteria for the expression. When I do this, this prompts me to manually enter values for all the fields I used in the expression. Not sure why it does this, but this is incorrect, it should draw the numbers from the other part of the Query.
Any thoughts on how to do this, to suppress zero results?
Thanks,
Jack

 

[pbaldy]

It may help to see the SQL, but I suspect you're running into the fact that the WHERE clause is evaluated before the SELECT clause, so the calculated fields are not known at that point. If you have:

NewField: Field1 + Field2

in SQL view instead of

WHERE NewField <> 0

you'd want

WHERE Field1 + Field2 <> 0

Paul
MS Access MVP 2007/2008

 

[Whitemtntn]

This is the expression I wrote.........

BalanceDue: (([ExpensesBilled]-[ExpensesReceived])+([ExpensesAdjusted]))+(([FeesBilled]-[FeesReceived])+([FeesAdjusted]))+(([InterestBilled]-[InterestReceived])+([InterestAdjusted]))

So do I just add the WHERE statememt after it?

 

[pbaldy]

If you look at the query in SQL view, does it say this?

WHERE BalanceDue <> 0

presuming so, staying in SQL view, try changing it to this:

WHERE (([ExpensesBilled]-[ExpensesReceived])+([ExpensesAdjusted]))+(([FeesBilled]-[FeesReceived])+([FeesAdjusted]))+(([InterestBilled]-[InterestReceived])+([InterestAdjusted])) <> 0


Paul
MS Access MVP 2007/2008

 

[Whitemtntn]

Appreciate the assistance. Still having trouble with this.
Here is the SQL.... The expression I wrote is in bold, I tried to add the WHERE statement but it doesn't like my syntax.


SELECT ARBill.ARBillNbr, ARBill.ARBillDate, Client.CliCode, Matter.MatCode, ARBill.ARBillFromDate, ARBill.ARBillToDate, IIf([Billed] Is Null,0,[Billed]) AS ExpensesBilled, IIf([ExpsRcvd] Is Null,0,[ExpsRcvd]) AS ExpensesReceived, IIf(I_LH_Adj.ExpensesAdjusted Is Null,0,I_LH_Adj.ExpensesAdjusted) AS ExpensesAdjusted, IIf([BilledAmt] Is Null,0,[BilledAmt]) AS FeesBilled, IIf([SumOfARFTRcvd] Is Null,0,[SumOfARFTRcvd]) AS FeesReceived, IIf(H_Fee_Billed.SumOfARFTAdj Is Null,0,H_Fee_Billed.SumOfARFTAdj) AS FeesAdjusted, (([FeesBilled]-[FeesReceived])+([FeesAdjusted])) AS FeeBalance, IIf(I_LH_Bill.SumOfLHTaxes1 Is Null,0,I_LH_Bill.SumOfLHTaxes1) AS Tax1Amt, IIf(I_LH_Bill.SumOfLHTaxes2 Is Null,0,I_LH_Bill.SumOfLHTaxes2) AS Tax2Amt, IIf(I_LH_Bill.SumOfLHTaxes3 Is Null,0,I_LH_Bill.SumOfLHTaxes3) AS Tax3Amt, ([Tax1Amt]+[Tax2Amt]+[Tax3Amt]) AS TaxesBilled, IIf(I_LH_PayAdj.TaxesReceived Is Null,0,I_LH_PayAdj.TaxesReceived) AS TaxesReceived, IIf(I_LH_Adj.TaxesAdjusted Is Null,0,I_LH_Adj.TaxesAdjusted) AS TaxesAdjusted, (([TaxesBilled]-[TaxesReceived])+([TaxesAdjusted])) AS TaxBalance, IIf(I_LH_Bill.InterestBilled Is Null,0,I_LH_Bill.InterestBilled) AS InterestBilled, IIf(I_LH_PayAdj.SumOfLHInterest Is Null,0,I_LH_PayAdj.SumOfLHInterest) AS InterestReceived, IIf(I_LH_Adj.InterestAdjusted Is Null,0,I_LH_Adj.InterestAdjusted) AS InterestAdjusted, (([InterestBilled]-[InterestReceived])+([InterestAdjusted])) AS InterestBalance, IIf(I_LH_Bill.SurchargeBilled Is Null,0,I_LH_Bill.SurchargeBilled) AS SurchargeBilled, IIf(I_LH_PayAdj.SumOfLHSurcharge Is Null,0,I_LH_PayAdj.SumOfLHSurcharge) AS SurchargeReceived, IIf(I_LH_Adj.SurchargeAdjusted Is Null,0,I_LH_Adj.SurchargeAdjusted) AS SurchargeAdjusted, (([SurchargeBilled]-[SurchargeReceived])+[SurchargeAdjusted]) AS SurchargeBalance, Matter.MatDateLastPaymt, (([ExpensesBilled]-[ExpensesReceived])+([ExpensesAdjusted]))+(([FeesBilled]-[FeesReceived])+([FeesAdjusted]))+(([InterestBilled]-[InterestReceived])+([InterestAdjusted])) AS BalanceDue
FROM (((((ARBill LEFT JOIN H_Exp_Billed ON ARBill.ARBillNbr = H_Exp_Billed.AREBillNbr) LEFT JOIN H_Fee_Billed ON ARBill.ARBillNbr = H_Fee_Billed.Bill) LEFT JOIN I_LH_PayAdj ON ARBill.ARBillNbr = I_LH_PayAdj.LHBillNbr) LEFT JOIN I_LH_Adj ON ARBill.ARBillNbr = I_LH_Adj.LHBillNbr) LEFT JOIN I_LH_Bill ON ARBill.ARBillNbr = I_LH_Bill.LHBillNbr) LEFT JOIN (Matter LEFT JOIN Client ON Matter.MatCliNbr = Client.CliSysNbr) ON ARBill.ARBillTo = Matter.MatBillTo
WHERE (((ARBill.ARBillDate) Between [Parameter 1] And [Parameter 2]));

 

[pbaldy]

Try

WHERE (((ARBill.ARBillDate) Between [Parameter 1] And [Parameter 2])) AND (([ExpensesBilled]-[ExpensesReceived])+([ExpensesAdjusted]))+(([FeesBilled]-[FeesReceived])+([FeesAdjusted]))+(([InterestBilled]-[InterestReceived])+([InterestAdjusted])) <> 0

None of those parentheses are required by the way. Access likes to add them, but in this instance they aren't necessary.

Paul
MS Access MVP 2007/2008

 

[Whitemtntn]

Tried it exactly like you said-- No go, it said there is a syntax error, missing operator.

Wouldn't I have to use the field name "BalanceDue" anyway? Otherwise how would it know to omit the zeroes from that specific field?

 

[PHV]

I'd use a WHERE clause like this:
WHERE (ARBill.ARBillDate Between [Parameter 1] And [Parameter 2])
AND (Nz(Billed,0)-Nz(ExpsRcvd)+Nz(ExpensesAdjusted,0)+Nz(BilledAmt,0)-Nz(SumOfARFTRcvd,0)+Nz(SumOfARFTAdj,0)+Nz(InterestBilled,0)-Nz(SumOfLHInterest,0)+Nz(InterestAdjusted,0))<>0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Whitemtntn]

PHV, that seems to work, thank you very much.
If it wouldn't be too much trouble, could you explain to me the code and why it worked?