Results 1 to 7 of 7
  1. #1
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98

    Excluding a Record from Report when 3 fields are null in a record


    Hi All,

    My report prints records which include Date, CustomerName and three category fields - each with a field showing the quantity of each of the three categories.

    Date CustomerName Item1 Item1QTY Item 2 Item2QTY Item3 Item3QTY

    I want to exclude from the report any customers where all Three category Quantities are Null.

    I can make it exclude the record if one of the categories is Null by using "<>0" in the Criteria field on the Query builder, but of course this means that records which have values in Item 2 & 3 categories are also excluded. I don't know how to build the expression which looks at all three fields before excluding the record...

    Basically, I need the record to be excluded from the report if "Item1Qty" AND "Item2Qty" AND "Item3Qty" are ALL Null.

    I've tried building a Query that does this job, but it's beyond me.

    Can anyone help?

    =Jimbo=

  2. #2
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85
    If null and zero are the same to you in this case then...

    Code:
    SELECT * FROM Table WHERE Nz(Item1QTY,0)=0 AND (Nz(Item2QTY,0)=0 AND Nz(Item3WTY,0)=0);
    Something like that.

  3. #3
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98
    Hi Dan,

    Thanks for the quick response... Yes. Null & Zero will be same...This looks right to me, but I'm still not sure where to put this expression ?? Can I type this directly into the Criteria box in the Query builder starting with "where" ??

    ??

    Jimbo

  4. #4
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85
    You know I've never actually used Reports. I assume that there built off of a query/table. If so, and assuming that I understood what your trying to do correctly I would put whatever your trying to have in the report in the Select statement, and the name of the table in the From statemenent. And then just copy and paste it into SQL. Let me play around with reports for a minute and see if I'm correct.

  5. #5
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85
    Okay it seems that I am right about how reports work, just copy and paste the sql into a new query, edit it as you wish, run it, save it. Click report and you should be good.

    I forgot that some people might not use sql alot. To build a query with SQL click View in the left corner, and go to SQL.

  6. #6
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85
    HAHAHA I JUST WENT FULL RETARD. Sorry about that, my code would have selected only the cases where all fields were null, forgot to include a NOT statement.


    code should be

    Code:
    SELECT * FROM Table WHERE NOT Nz(Field1,0)=0 AND (Nz(Field2,0)=0 AND Nz(Field3,0)=0);

  7. #7
    Jamescdawson is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    South Wales, UK
    Posts
    98
    I've tried this and it just isn't working for me. I'm pretty sure I'm not putting it in the right place.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 05-25-2012, 02:40 AM
  2. Replies: 2
    Last Post: 11-16-2011, 07:38 PM
  3. Populating Null Data with Next Available Record
    By Kimbertha in forum Programming
    Replies: 6
    Last Post: 09-25-2010, 11:02 AM
  4. Excluding null values on SQL table query
    By DB2010MN26 in forum Queries
    Replies: 1
    Last Post: 09-03-2010, 12:54 PM
  5. Null vs New Record
    By jversiz in forum Access
    Replies: 2
    Last Post: 02-20-2008, 12:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums