Criteria syntax in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have 12 fields -- one for each month of the year that have dollar figures
in them. I want to filter out all the records that have zeros ACROSS THE
BOARD. Currently, I have <>0 in SAME criteria line of each field but it
seems to filter out the records that just have zeros in SOME of the months.
How can I fix this?

Thank you!
MN
 
Create a calculated field to sum the figures for all of the months. If the
sum = 0, exclude the record. Is the query not working like you want it to
because of Null values? If so, use the function Nz().
 
If there are positive and negative values the total could be zero.
Try using two queries. First one set criteria for all fields at zero to
pull a list of all records with all zero.
In second query left join the table to the first query and criteria of NULL.
It will not pull any records that the first query found to be all zeros.
 
Try using multiple lines of criteria. What you seem to want is to return
the row if any of the fields is not zero. What you are doing is returning a
row if all the fields are not zero.,

Something like:
Field: Month1
Criteria(1) : <> 0

Field: Month2
Criteria(1):
Criteria(2): <> 0

Field: Month3
Criteria(1):
Criteria(2):
Criteria(3): <> 0

If you run out of criteria lines, you can add more by selecting Insert: Row
from the menu bar.
 
You're right. He could use the ABS() function to return positive values only
or simply concatenate the values to look for an exact match to String(12,"0").


KARL said:
If there are positive and negative values the total could be zero.
Try using two queries. First one set criteria for all fields at zero to
pull a list of all records with all zero.
In second query left join the table to the first query and criteria of NULL.
It will not pull any records that the first query found to be all zeros.
Create a calculated field to sum the figures for all of the months. If the
sum = 0, exclude the record. Is the query not working like you want it to
Click to expand...
[quoted text clipped - 10 lines]
Click to expand...
 
Back
Top