473,323 Members | 1,589 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Suppress records in Access

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
12 2819
NeoPa
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
@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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
Jennifer,

That's because you need to change all the ANDs to ORs if you want the full inverse.
Jun 7 '09 #7
OldBirdman
675 512MB
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
32,556 Expert Mod 16PB
@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
OldBirdman
675 512MB
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
It's a pleasure Jennifer. Thanks for the update :)
Jun 13 '09 #13

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

Similar topics

2
by: Emilio Jimonerz | last post by:
Hello. I am developing a project tracking database that records the following information: 1. Project name 2. Lead person(s) 3. Support person(s) 4. Clients 5. Contacts The data is organized...
4
by: Susan Bricker | last post by:
Hi. I have a routine for the AfterUpdate event of a checkbox on my Main Menu. The checkbox causes a "User Variable" (in a table for UserSettings) to be modified ('Y' or 'N') by issuing an...
1
by: Ken Morris | last post by:
Any ideas on how to suppress the plus sign (+) beside a DataGrid row when that particular parent row has no records in the corresponding child table? This applies to a datagrid for a Windows...
3
by: Bri | last post by:
AC97 on WinXP, Image control on a Form and on a Report. I have followed the advice on the following MVPS page: http://www.mvps.org/access/api/api0038.htm This states that after changing the...
1
by: Shaggers | last post by:
I need to hide/not display a blank row on a subform. The Main form is based on a table, contains Site Header information The Subform is based on another table containing Sample Details records ...
1
by: jaishu | last post by:
When i try to insert data into a backend Oracle table (with ODBC) using a form, (i use docmd.runsql "insert....") Access gives me a message like"You are trying to append 1 row to the table are you...
2
by: keithsimpson3973 | last post by:
Hi, working in Access 2003. In my report, I have a field that contains a qty. If the qty is equal to zero , then I would like access not to display that record on the report. I thought you could do...
4
by: lory | last post by:
Hi, whan I have for example a vba script stSql = "INSERT INTO table..." DoCmd.RunSQL stSql associated with a command, and then I click on it there is an access popup that informs me how many...
0
by: dragon52 | last post by:
Hi there, I have records in the form of scorecards. Each scorecard has fields "type" and "score". eg typeA 2.3 typeB 3.3 typeA 2.0 typeC 1.9 typeB 1.1
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.