query count help

  • Thread starter Thread starter SoggyCashew
  • Start date Start date
S

SoggyCashew

Hello, I have a query that gives me the sum for a few fields. Some of the
fields will return a zero. How do I not show the fields containing a zero in
my query results? Would i use the critera section and if so what would I
enter there?
 
Hi

If you do not want to include fields that contain null (nothing)
SELECT TableName.Yourfield
FROM TableName
WHERE (((TableName.Yourfield) Is Not Null));

Put
Is Not Null
in the criteria row - note this will allow 0

____________________
If you do not want to include fields that contain 0
SELECT TableName.Yourfield
FROM TableName
WHERE (((TableName.Yourfield)<>0));

Put
<>0
in the criteria row - this will also remove the nulls

You will still see the results of the query (you may have some that contain
a 0). To remove these you could design a report and also show results <>0
(tha do not contain 0)


So - a better method would be to NOT include any field contents that contain
0 -
Normally you would have this is the field
Yourfield

You only want to include field with anything (could be positive or negative)
that is not 0 so use this instead of YourField (in the field row)

FieldName: IIf([TableName]![Yourfield]<>0,[TableName]![Yourfield])

This would look like this in sql

SELECT Sum(IIf([TableName]![Yourfield]<>0,[TableName]![Yourfield])) AS
FieldName
FROM TableName;


Good luck
 
Sorry just re read your question
the last bit should be

FieldName: Count(IIf([TableName]![Yourfield]<>0,[TableName]![Yourfield]))

Note Count - not Sum
 
Wayne, my fields are Yes/No check boxes. will this formula still count?
--
Thanks,
Chad


Wayne-I-M said:
Sorry just re read your question
the last bit should be

FieldName: Count(IIf([TableName]![Yourfield]<>0,[TableName]![Yourfield]))

Note Count - not Sum

--
Wayne
Manchester, England.



SoggyCashew said:
Hello, I have a query that gives me the sum for a few fields. Some of the
fields will return a zero. How do I not show the fields containing a zero in
my query results? Would i use the critera section and if so what would I
enter there?
Click to expand...
Click to expand...
 
Back
Top