Results 1 to 3 of 3
  1. #1
    crimedog is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Posts
    141

    Query Builder - If X is not blank only include if Y is not blank...

    1
    1 3



    Using the Criteria line on the query builder, lets say I have two columns X and Y
    - I need to see ALL of the records where X is Blank
    -PLUS ONLY the records where X is Not Blank AND Y Is not Blank

    - from the above example I would only see row 2 and 3

    So I have tried (under Column Y): Like IIF(Isnull([X],"*",Not Is Null)

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    which col is X and which is Y? you didnt label.
    you can always make 2 queries 1 for each rule
    then put them together in a union query

    Q1 : select * from table where X=""
    Q2: select * from table where Y<>"" and X <> ""

    union qry:
    select * from Q1
    union
    select * from Q2

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try a query along these lines

    Code:
    SELECT *
    FROM myTable
    WHERE (X is null) OR (X is not null and Y is not null)
    this is assuming data is per your example - i.e. numeric

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

Similar Threads

  1. Replies: 1
    Last Post: 01-31-2018, 04:06 PM
  2. Include "blank" child records.
    By Homegrownandy in forum Access
    Replies: 5
    Last Post: 11-10-2015, 06:49 PM
  3. How to include blank records in reports
    By saudkazia in forum Reports
    Replies: 14
    Last Post: 10-12-2015, 05:06 PM
  4. Replies: 4
    Last Post: 01-24-2013, 12:11 AM
  5. Replies: 1
    Last Post: 09-05-2008, 12:07 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