454,226 Members | 1,435 Online
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 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. May 31 '09 #1
12 Replies

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

 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 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. May 31 '09 #3

 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 SELECT * FROM Table1 WHERE ([Col 4] + [Col 5] + [Col 6] + [Col 7] + [Col 8])<>0; May 31 '09 #4

 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 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)); Jun 7 '09 #6

 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 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

 Expert Mod 15k+ P: 31,707 @OldBirdman I'm sorry OB, Could you explain how your : Expand|Select|Wrap|Line Numbers 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 : Expand|Select|Wrap|Line Numbers 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 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

 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

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