By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,226 Members | 1,435 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,226 IT Pros & Developers. It's quick & easy.

Suppress records in Access

P: 15
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:
Expand|Select|Wrap|Line Numbers
  1. Col 1,  col 2,  col 3,  col 4,  col 5,  col 6,  col7,  col 8
  2. 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.
May 31 '09 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,707
You could use a string value in your WHERE clause like :
Expand|Select|Wrap|Line Numbers
  1. WHERE [Col 4] & [Col 5] & [Col 6] & [Col 7] & [Col 8]='00000'
Welcome to Bytes!
May 31 '09 #2

NeoPa
Expert Mod 15k+
P: 31,707
A more standard, basic, approach would be to check each field with an 'AND' between each :
Expand|Select|Wrap|Line Numbers
  1. WHERE [Col 4]=0
  2.   AND [Col 5]=0
  3.   AND [Col 6]=0
  4.   AND [Col 7]=0
  5.   AND [Col 8]=0
Notice that these values are numeric so have no quotes around the 0s.
May 31 '09 #3

ADezii
Expert 5K+
P: 8,679
@NeoPa
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Table1
  2. WHERE ([Col 4] + [Col 5] + [Col 6] + [Col 7] + [Col 8])<>0;
May 31 '09 #4

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jun 1 '09 #5

P: 15
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
Expand|Select|Wrap|Line Numbers
  1. WHERE (((tbl_Structure.Year)=[Enter the current year: yyyy])
  2.   AND  ((tbl_Structure.Consol) Is Not Null)
  3.   AND  ((Val(Nz([L29],0)))<>0)
  4.   AND  ((Val(Nz([Qtr1Amt],0)))<>0)
  5.   AND  ((Val(Nz([Qtr2Amt],0)))<>0)
  6.   AND  ((Val(Nz([Qtr3Amt],0)))<>0)
  7.   AND  ((Val(Nz([Qtr4Amt],0)))<>0));
Jun 7 '09 #6

NeoPa
Expert Mod 15k+
P: 31,707
Jennifer,

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

100+
P: 675
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

Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 7 '09 #8

NeoPa
Expert Mod 15k+
P: 31,707
@OldBirdman
I'm sorry OB, Could you explain how your :
Expand|Select|Wrap|Line Numbers
  1. WHERE NOT ([Col 4]=0
  2.   AND      [Col 5]=0
  3.   AND      [Col 6]=0
  4.   AND      [Col 7]=0
  5.   AND      [Col 8]=0)
is any different functionally from my suggestion :
Expand|Select|Wrap|Line Numbers
  1. WHERE [Col 4]<>0
  2.    OR [Col 5]<>0
  3.    OR [Col 6]<>0
  4.    OR [Col 7]<>0
  5.    OR [Col 8]<>0
I can only assume there has been some form of misunderstanding.
Jun 7 '09 #9

100+
P: 675
You are correct, the results are identical. However, not a misunderstanding 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.
Jun 8 '09 #10

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jun 8 '09 #11

P: 15
NeoPa and OldBirdman,
Thanks so much for your help. The "OR" in place of "And" did the trick.
Thanks again for your exertise.
Jennifer
Jun 13 '09 #12

NeoPa
Expert Mod 15k+
P: 31,707
It's a pleasure Jennifer. Thanks for the update :)
Jun 13 '09 #13

Post your reply

Sign in to post your reply or Sign up for a free account.