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