Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2016
    Posts
    2

    Display all records including NULL values in a query using IIF is not working?

    Hello.



    My question is:

    I can list all records including the null valued records in a query via placing the path to the table containing all the records on the Criteria row, but when I add IIF(condition, true-condition, false-condition) and use the same expression that worked in one of the arguments for the IIF function, I see all records but not the NULL valued ones?

    I basically took the working condition and added the IIF() function around it thinking that it would give the same result but it did not.

    Is there something that I am doing wrong or missing?

    What I would like to do is:

    1. I have a table that has a field called SDS which is formatted as a number field, has an input mask of 00000000 and is filled with 8 digit numbers as well as NULL values. ( some of the records do not have an SDS number and so they are blank. )

    2. I have a main form that has a combo box that is linked to the SDS field so that it shows all the numbers in it's drop down list, minus the null value.

    3. when i select a number from the combo box drop down menu, the form displays all the records associated with that selected number. ( this is working )

    4. when I clear the combo box, I want the form to display all records including null entries. ( this part is not working yet )

    5. I was using the IIF() to do the work via: IIF([forms]![ReportsFault]![SrchSds], [forms]![ReportsFault]!pSrchSds], [Form]![SDS])

    ( the iif statement above works fine for everything except that when I clear the combo box, all rows are displayed except for NULL valued rows )

    ( If i remove the IIF() function and just put [Forms]![SDS] is NULL or is not Null then I get all values including the Null ones )

    ( If i use IIIF([forms]![ReportsFault]![SrchSds], [forms]![ReportsFault]![SrchSds], ([Form]![SDS]))

    I have a combo box that is referencing a table/field that contains 8 digit numbers and also some are not populated. (NULL values)

    I would like to display a selection of records depending on what value is selected from the combo box. (this part is working)

    I would like to display all entries in the table when the combo box is cleared with NULL value. (this is the issue)

    In my query, if I put [forms]![SDS] Is Null or Is Not Null in the criteria section, I get all the records including the null valued ones.

    If i put iif([Forms]![ReportsFault]![SrchSds], [Forms]![ReportsFault]![SrchSds], (([Form]![SDS]) is Null or is not Null) ) the result shows nothing?

    I was hoping to simply copy the working statement from above into the IIF() function as one of the arguments but it is not working.

    Can someone help me out with this issue?

    Thanks.

    - joe

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First, Welcome to the forum....

    I am lost on your explanation. You are making this way too hard (IMO).

    Your use of the IIF() function is wrong.
    This syntax is wrong: IIF(condition, true-condition, false-condition)

    Right: IIF(condition, truepart, falsepart)
    Example: CheckIt = IIf(TestMe > 1000, "Large", "Small")


    IIF([forms]![ReportsFault]![SrchSds], [forms]![ReportsFault]!pSrchSds], [Form]![SDS])
    You don't have a condition and the false part is just the collection and a form name (in red) - no control, no value.


    Questions:
    What is the field data type for "SDS"? (Number or Text?)
    What is the VBA code for the combo box?
    What is the main form name.
    What is the record source for the main form? (query or table?)



    I threw together a demo of what I think you are trying to do. A table, two queries, a form and about 5 lines of VBA.

    Is this close to what you want???
    Attached Files Attached Files

  3. #3
    Join Date
    Aug 2016
    Posts
    2

    using iif() to filter a combobox selection but returning NULL if the combo is empty doesn't work.

    Thanks for your reply, Steve.

    Can I ask another question related to the IIF() statement used in the access sql builder that goes along with my question.


    Ok I'll try to make this a bit clearer...

    1. I have a table called FAULTS, which has SDS, OPERATOR, PROGRAM and FAULT_TYPE as field columns.

    The SDS column is populated with numbers, the OPERATOR, PROGRAM and FAULT_TYPE have Strings.

    Some of the records in each column have no entries, (NULL).


    2. I have a front end form that opens another form called "FAULTLIST" which has 4 comboBoxes called srchSds, srchOperator, srchProgram, srchFaultType

    These combo boxes use a Select from distinct row Group as is not null, query to populate the values from the database table. ( so srchSDS combobox has a list of all the values minus duplicates and null, srchOperator has the same but taken from the OPERATOR column, etc, etc for the other boxes )

    I have a listbox also on this form that has a query for it's recordSource and it populates based on what is selection in the comboBoxes. ( so if I select 00123456 from the srchSds box, and 'Jimmy' from the srchOperator box, and leave the rest empty, the listbox query should populate all records that have 00123456 in SDS as well as 'Jimmy' in the OPERATOR )

    In the listbox query, I have iif([Forms]![Faults]![srchSds], [Forms]![FaultList]![srchSds], [FAULTS]![SDS]) showing in the sql builder. ( I also have the same logic for OPERATOR, PROGRAM and FAULT_TYPE.... substituting the srchSds with their corresponding comboboxes and table columns. )

    Basically this is saying if the comboBox has a value, then return that value else return all the values from the source table.

    Everything is working properly with this SQL statement, however while I was testing it out, I noticed that there were some records missing from the listbox query, more specifically the empty / NULL values.


    skipping ahead,

    I decided to do a test using a different SQL statement to see if I could get the listbox to display empty / NULL values so I deleted the Query and started over. This time I put "IS NULL" in the Condition row instead of the IIF(....) statement and when I ran the report, all the NULL values were displayed. ( great, I thought, all I have to do is wrap the IIF() around the "IS NULL" statement and i'm good to go. )

    However, when I tried to do so, there were no results shown.

    As an example, I also put a super simple sql statement by using : iif(true, [Forms]![FindFaults]![srchSds] IS NULL, 0) but no results.

    I also tried iif(true, [Forms]![FindFaults]![srchSds] or Is NULL, 0)

    but no results either.

    so my question is:

    why do you think " IS NULL " or " OR IS NULL " stops working when in an iif() function but it works fine when I put it straight in the conditions row or in the "or" row it works fine?

    I hope this isn't too much confusion for you.. (-:

    Thanks for your help.

    Joe



    Quote Originally Posted by ssanfu View Post
    First, Welcome to the forum....

    I am lost on your explanation. You are making this way too hard (IMO).

    Your use of the IIF() function is wrong.
    This syntax is wrong: IIF(condition, true-condition, false-condition)

    Right: IIF(condition, truepart, falsepart)
    Example: CheckIt = IIf(TestMe > 1000, "Large", "Small")



    You don't have a condition and the false part is just the collection and a form name (in red) - no control, no value.


    Questions:
    What is the field data type for "SDS"? (Number or Text?)
    What is the VBA code for the combo box?
    What is the main form name.
    What is the record source for the main form? (query or table?)



    I threw together a demo of what I think you are trying to do. A table, two queries, a form and about 5 lines of VBA.

    Is this close to what you want???

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post the SQL from the query?
    In query design, right click on the query hear then select SQL View.
    Copy and paste so we can see what you have.

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Oops! - sorry, Orange - your post wasn't there when I started my reply!


    iif([Forms]![Faults]![srchSds], [Forms]![FaultList]![srchSds], [FAULTS]![SDS])
    Two problems with that Iif.

    if the comboBox has a value
    ,

    First, the part in red doesn't do that. That expression will evaluate to True/False, and it will always be False unless it happens to be -1. It doesn't check for Null.

    To check for Null, use isnull([Forms]![Faults]![srchSds])

    else return all the values from the source table.
    The part in green doesn't do that. An Iif can only return one value, not a set of values.



    why do you think " IS NULL " or " OR IS NULL " stops working when in an iif() function
    Is Null can only be used in the condition part of the Iif(), not in the True or False parts

    Please post the entire SQL of one of the list boxes, to make it easier for us to diagnose your problem.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    1. I have a table called FAULTS, which has SDS, OPERATOR, PROGRAM and FAULT_TYPE as field columns.

    The SDS column is populated with numbers, the OPERATOR, PROGRAM and FAULT_TYPE have Strings.
    But you say
    ( so if I select 00123456 from the srchSds box,
    A number type field cannot have leading zeros - unless you have used formatting to add the leading zeros.
    Open the table "FAULTS" in design view, find the field name "SDS" - what is the "Data Type"? Number or Text?

    -------------------------------------------------------------

    So this is what I know so far:
    You have a table named "FAULTS".
    There are 4 fields:
    - SDS (type Number) (??)
    - OPERATOR (type Text)
    - PROGRAM (type Text)
    and
    - FAULT_TYPE (type Text)

    There is a form named (unknown) that opens
    a form named "FAULTLIST", with a record source of (unknown - maybe the form is unbound).
    On the form "FAULTLIST", there are 4 combo boxes named "srchSds", "srchOperator", "srchProgram" and "srchFaultType".
    The Row Sources for the combo boxes are like
    Code:
    SELECT FAULTS.SDS FROM FAULTS WHERE (((FAULTS.SDS) Is Not Null)) ORDER BY FAULTS.SDS;
    but changed for their respective field names.

    On the form "FAULTLIST", there is also a list box named (unknown) with a row source of (unknown query).
    The list box Row Source is filtered by the combo boxes.

    Good so far.

    Now my problem...
    This filter for the SDS field is: "iif([Forms]![Faults]![srchSds], [Forms]![FaultList]![srchSds], [FAULTS]![SDS])"

    The syntax for the Immediate If function is: IIF(condition, truepart, falsepart)

    The condition must evaluate to TRUE or FALSE (-1 or 0). Your condition is: "[Forms]![Faults]![srchSds]".
    The problem is that is is saying "Look in the forms collection for the form name 'FAULTS' and return the value of the control named 'srchSds' ".
    But "FAULTS" is a table, not a form! And the control "srchSds" is on the form "FAULTLIST". You cannot refer to a table using the forms collection.
    So the condition probably evaluates to 0.

    The TRUE_Part is: [Forms]![FaultList]![srchSds]
    This will return the value selected in the combo box.
    Good...

    The FALSE_Part is:[FAULTS]![SDS]
    You cannot refer to a table like this.

    I just don't see how the filters can work...



    Would you post your dB for analysis??

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

Similar Threads

  1. Query Criteria not including null values
    By shaun_za in forum Queries
    Replies: 1
    Last Post: 09-09-2015, 07:01 AM
  2. Replies: 7
    Last Post: 04-02-2015, 07:25 AM
  3. Working with queries and null values
    By guidout in forum Queries
    Replies: 6
    Last Post: 03-26-2015, 05:53 PM
  4. Filter fields including records with null
    By Ruegen in forum Programming
    Replies: 18
    Last Post: 01-28-2014, 11:23 PM
  5. Query including Null relationship?
    By David Criniti in forum Database Design
    Replies: 0
    Last Post: 08-14-2009, 09:10 PM

Tags for this Thread

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