Suppress records in Access

[jenniferhelen]

I would like to suppress a row of data if each of my 5 currency columns contains a zero. An example of 1 record is below:

Col 1,  col 2,  col 3,  col 4,  col 5,  col 6,  col7,  col 8
Smith,  2008,   qtr4,     0,      0,      0,      0,     0

So if columns 4 through 8 all contain zeros then I don’t want this record to appear in my report. I am not sure if this is even possible as I have been unable to find any documentation close to what I am trying to accomplish. I greatly appreciate any guidance.

[NeoPa]

You could use a string value in your WHERE clause like :

WHERE [Col 4] & [Col 5] & [Col 6] & [Col 7] & [Col 8]='00000'

Welcome to Bytes!

[NeoPa]

A more standard, basic, approach would be to check each field with an 'AND' between each :

WHERE [Col 4]=0
  AND [Col 5]=0
  AND [Col 6]=0
  AND [Col 7]=0
  AND [Col 8]=0

Notice that these values are numeric so have no quotes around the 0s.

[ADezii]

You could use a string value in your WHERE clause like :

WHERE [Col 4] & [Col 5] & [Col 6] & [Col 7] & [Col 8]='00000'

Welcome to Bytes!

Just for curiosity, NeoPa, since Columns 4 thru 8 are the CURRENCY Data Type, wouldn't the following be more efficient and practical. I think the OP wants to suppress, not include, those Values also:

SELECT * FROM Table1
WHERE ([Col 4] + [Col 5] + [Col 6] + [Col 7] + [Col 8])<>0;

[NeoPa]

If you know that none of the values can be negative, then that would work fine ADezii.

That wasn't stated in the question though, and I try to avoid assumptions where possible.

It's good that the option is there though, as long as the OP (and all other readers) understand the caveat.

[jenniferhelen]

Thank you for your suggestions. I have them half working. When I set the criteria in the five columns to = 0, the result is 224 records. So now I know I want to surpress 224 records out of 569 total records. However when I change the =0 to <>0, I do not get any results. My where statement is below.
The data in these columns may offset each other so I am unable to use ADezil's suggestion. I appreciate very much your comments.
Thanks,
Jennifer

WHERE (((tbl_Structure.Year)=[Enter the current year: yyyy])
  AND  ((tbl_Structure.Consol) Is Not Null)
  AND  ((Val(Nz([L29],0)))<>0)
  AND  ((Val(Nz([Qtr1Amt],0)))<>0)
  AND  ((Val(Nz([Qtr2Amt],0)))<>0)
  AND  ((Val(Nz([Qtr3Amt],0)))<>0)
  AND  ((Val(Nz([Qtr4Amt],0)))<>0));

[NeoPa]

Jennifer,

That's because you need to change all the ANDs to ORs if you want the full inverse.

[OldBirdman]

The opposite of

WHERE [Col 4]=0
AND [Col 5]=0
AND [Col 6]=0
AND [Col 7]=0
AND [Col 8]=0

is not to replace "AND" with "OR", but

WHERE NOT ([Col 4]=0 AND [Col 5]=0 AND [Col 6]=0 AND [Col 7]=0 AND [Col 8]=0)

Your statement WHERE a1<>0 AND a2<>0 ... will only evaluate to TRUE if all the values are not zero.

The "Val" function in your last post should be unnecessary if the table contains numeric data. Val() changes text to a number using an uncommon algorithm. If this, or the NZ function are necessary, then the table design might need to be reviewed.

[NeoPa]

The opposite of

WHERE [Col 4]=0 
  AND [Col 5]=0 
  AND [Col 6]=0 
  AND [Col 7]=0 
  AND [Col 8]=0

is not to replace "AND" with "OR", but

WHERE NOT ([Col 4]=0 AND [Col 5]=0 AND [Col 6]=0 AND [Col 7]=0 AND [Col 8]=0)

Your statement WHERE a1<>0 AND a2<>0 ... will only evaluate to TRUE if all the values are not zero.

I'm sorry OB, Could you explain how your :

WHERE NOT ([Col 4]=0
  AND      [Col 5]=0
  AND      [Col 6]=0
  AND      [Col 7]=0
  AND      [Col 8]=0)

is any different functionally from my suggestion :

WHERE [Col 4]<>0
   OR [Col 5]<>0
   OR [Col 6]<>0
   OR [Col 7]<>0
   OR [Col 8]<>0

I can only assume there has been some form of misunderstandin g.

[OldBirdman]

You are correct, the results are identical. However, not a misunderstandin g but my mind computing incorrectly. I should know better than to question your SQL statements, which clearly demonstrate how much I don't know.

My apologies to jenniferhelen if I caused any confusion.

[NeoPa]

You needn't be too hard on yourself OB. I had two such situations over the weekend where I'd missed something.

It's much better to post and make a few mistakes, than not to post at all. All your posts are appreciated.

[jenniferhelen]

NeoPa and OldBirdman,
Thanks so much for your help. The "OR" in place of "And" did the trick.
Thanks again for your exertise.
Jennifer

[NeoPa]

It's a pleasure Jennifer. Thanks for the update :)