Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how do i skip records that have zero calculated values?

Status
Not open for further replies.
wvdba

wvdba

IS-IT--Management
Jun 3, 2008
465
US
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?
 
Sort by date Sort by votes
This might be a lot easier if you use a normalizing query as your source instead of this IIF statement. having fields like credit_debit_amt1, credit_debit_amt2,...,credit_debit_amt10 is a red flag that your tables aren't normalized.

What does your source table look like? What results do you want from that source?

Leslie

Have you met Hardy Heron?
 
Upvote 0 Downvote
well, this is a massive database that i inherited. the management wants the report friday. anything new, that's being added, i heed your advice and have used normalization principles. but this one is a deadline. and i am trying to find out how to eliminate records when balance_due is zero.
thanks.
 
Upvote 0 Downvote
Generally you would
Code: 
WHERE 

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))) <> 0
But, as Leslie said, the complete SQL statement would give us a better picture.
 
Upvote 0 Downvote
ok, thanks. here's the entire sql:
Code: 
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 (((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;
 
Upvote 0 Downvote



Code: 
...
WHERE (((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]))[b]
  AND (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))>0[/b]
...

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
golom,
that didn't work. it's still pulling records with balance_due of zero.
 
Upvote 0 Downvote




Got the wrong one, DUH!
Code: 
  AND 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)))>0


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
What are the data types of those fields?

If they are floating-point data types (i.e. Double or Single) then testing for zero will almost never work because decimal values cannot always be exactly represented by floats.

Try something like
Code: 
WHERE
       CDec ([red]... that calculation ...[/red]) <> 0

or

Code: 
WHERE
       Abs ([red]... that calculation ...[/red]) < 0.00001
 
Upvote 0 Downvote
thanks golom.
the data types for the fields from the table are currency.
but i'm not sure what the "calculated fields" would be.
the code is still producing zero balance_due records.
i'm going to try your last suggestion.
 
Upvote 0 Downvote
still not working. it's pulling zero-balance rows.
 
Upvote 0 Downvote



Please post your current SQL.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
here's my sql. it selects records with balance_due of zero. i want to skip those.

Code: 
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 (((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;
 
Upvote 0 Downvote



Where is the criteria that I posted in my DUH post???

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
i put that in, but didn't make any difference, so i posted the original sql.
 
Upvote 0 Downvote



"so i posted the original sql"

WHY??? You already posted that! What help does that do?

Please post the SQL that resulted on your post, "still not working. it's pulling zero-balance rows."

PLEASE!

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
here's what doesn't work including your "DUH" part.this one
doesn't even give any rows at all.
Code: 
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 

AND 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)))>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;
 
Upvote 0 Downvote




You cannot start a WHERE clause with AND. You forgot the portion BEFORE the DUH post text.

REMOVE the BOLDED text, in the post preceeding the DUH, and replace with the DUH text.

Figure it out!

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
the "AND" is not in the query. it's just copy/past error. it's not producing any rows. sorry. i tried it exactly as you showed here. it produces the same results. rows with zero balance due are coming out. maybe since those values are currency, it never holds true for a ZERO. i'm not sure.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

YoBi4Ever
  • Question
I would like to compute how many minutes or hours the employee left work by comparing two records
Replies
3
Views
160
dhookom
dhookom
TheresAlwaysAWay
  • Locked
  • Question
How can I make Access display a + sign in front of positive integers, just as it does - for neg? 2
Replies
8
Views
3K
TheresAlwaysAWay
TheresAlwaysAWay
atadbitslow
  • Locked
  • Question
how to search data and display matches that include the user's input, but not necessarily match
Replies
5
Views
385
atadbitslow
atadbitslow
lmcate
  • Locked
  • Question
How to Open ACCESS (MDB) from an HTML Page 1
Replies
2
Views
3K
lmcate
lmcate
renigar
  • Locked
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
677
renigar
renigar

Part and Inventory Search

Sponsor

Back
Top