how do i skip records that have zero calculated values?

[wvdba]

hi,
i have part of a query that looks like this: it calculates a calculated field. however, i want to skip records when this calculated field is zero. how can i accomplish that, please?
IIf([check_amt] Is Null,(0),
(Nz([invoice_amt],0)+Nz([credit_debit_amt1],0)+
Nz([credit_debit_amt2],0)+Nz([credit_debit_amt3],0)+
Nz([credit_debit_amt4],0)+Nz([credit_debit_amt5],0)+
Nz([credit_debit_amt6],0)+Nz([credit_debit_amt7],0)+
Nz([credit_debit_amt8],0)+Nz([credit_debit_amt9],0)+
Nz([credit_debit_amt10],0)-Nz([check_amt],0)-
Nz([check_amt_2],0)-Nz([check_amt_3],0)-
Nz([check_amt_4],0)-Nz([check_amt_5],0))) AS balance_due
the balance_due is not in the database, it's a calculated field. when i reference in the query, it opens up an input box. any ideas?

 

[SkipVought]

Please post EXACTLY what you have in your SQL!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wvdba]

here's the whole sql:

SELECT 
tbl_perdiem_accounts.perdiem_acct, 
tbl_perdiem_accounts.fiscal_year, 
tbl_perdiem_accounts.org_facility, 
tbl_facility_code.facility, 
tbl_perdiem_accounts.invoice_date, 
tbl_perdiem_accounts.month_of_service, 

IIf(([fee_type]="transfer"),
([student_days]*56),
(tbl_perdiem_accounts.student_days*[fee])) AS invoice_amt, 

(Nz([invoice_amt],0)+
Nz([credit_debit_amt1],0)+
Nz([credit_debit_amt2],0)+
Nz([credit_debit_amt3],0)+
Nz([credit_debit_amt4],0)+
Nz([credit_debit_amt5],0)+
Nz([credit_debit_amt6],0)+
Nz([credit_debit_amt7],0)+
Nz([credit_debit_amt8],0)+
Nz([credit_debit_amt9],0)+
Nz([credit_debit_amt10],0)) AS total_month_amt, 

Nz([check_amt],0)+
Nz([check_amt_2],0)+
Nz([check_amt_3],0)+
Nz([check_amt_4],0)+
Nz([check_amt_5],0) AS total_check_amt, 

IIf([check_amt] Is Null,(0),
(Nz([invoice_amt],0)+
Nz([credit_debit_amt1],0)+
Nz([credit_debit_amt2],0)+
Nz([credit_debit_amt3],0)+
Nz([credit_debit_amt4],0)+
Nz([credit_debit_amt5],0)+
Nz([credit_debit_amt6],0)+
Nz([credit_debit_amt7],0)+
Nz([credit_debit_amt8],0)+
Nz([credit_debit_amt9],0)+
Nz([credit_debit_amt10],0)-
Nz([check_amt],0)-
Nz([check_amt_2],0)-
Nz([check_amt_3],0)-
Nz([check_amt_4],0)-
Nz([check_amt_5],0))) AS balance_due


FROM tbl_facility_code INNER JOIN (Perdiem_facility_Code INNER JOIN (tbl_perdiem_accounts INNER JOIN tbl_fee_code ON tbl_perdiem_accounts.fiscal_year = tbl_fee_code.fiscal_year) ON Perdiem_facility_Code.Perdiem_Acct = tbl_perdiem_accounts.perdiem_acct) ON tbl_facility_code.facility_code = tbl_perdiem_accounts.org_facility

WHERE 

(Nz([invoice_amt],0)+
Nz([credit_debit_amt1],0)+
Nz([credit_debit_amt2],0)+
Nz([credit_debit_amt3],0)+
Nz([credit_debit_amt4],0)+
Nz([credit_debit_amt5],0)+
Nz([credit_debit_amt6],0)+
Nz([credit_debit_amt7],0)+
Nz([credit_debit_amt8],0)+
Nz([credit_debit_amt9],0)+
Nz([credit_debit_amt10],0)-
Nz([check_amt],0)-
Nz([check_amt_2],0)-
Nz([check_amt_3],0)-
Nz([check_amt_4],0)-
Nz([check_amt_5],0)) > 0 AND 


((tbl_perdiem_accounts.perdiem_acct) Like "*" & [Enter Perdiem Account] & "*") AND 
((tbl_perdiem_accounts.fiscal_year) Between [Enter Beginning Fiscal Year] And [Enter Ending Fiscal Year])
ORDER BY tbl_perdiem_accounts.fiscal_year, tbl_perdiem_accounts.org_facility, tbl_perdiem_accounts.invoice_date;

 

[SkipVought]

Are you looking at the code, too and trying to figure things out??? Seems I may have missed copying the first line of the Balance_Due code in your SELECT clause...

WHERE [b]
IIf([check_amt] Is Null,(0),[/b]
(Nz([invoice_amt],0)+
Nz([credit_debit_amt1],0)+
Nz([credit_debit_amt2],0)+
Nz([credit_debit_amt3],0)+
Nz([credit_debit_amt4],0)+
Nz([credit_debit_amt5],0)+
Nz([credit_debit_amt6],0)+
Nz([credit_debit_amt7],0)+
Nz([credit_debit_amt8],0)+
Nz([credit_debit_amt9],0)+
Nz([credit_debit_amt10],0)-
Nz([check_amt],0)-
Nz([check_amt_2],0)-
Nz([check_amt_3],0)-
Nz([check_amt_4],0)-
Nz([check_amt_5],0)))
...

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]