Getting IIf to return a null value if True (1 Viewer)


New member
Local time
Today, 09:53
May 28, 2020
Howdy all! I've tried hard to scour the web for an answer before posting, because no one likes answering the same question twice! I've not found anything but I am happy to be re-directed. ? The query is looking to either display all the cases in the table that do not have a close date (CloseDate= Is Null), or all the cases regardless of whether or not they have a close date based on a control on a form.

I have a table with OpenDate, CloseDate, COEMR (key), FirstName, LastName, CaseType and several other variables (not necessary for what i''m talking about here, but the picture shows the full range). The query results are displayed on the form FrmTherapyRef. There is a control on the form, OpenRefCheck (a checkbox) that I would like to control the query. The query is only looking at cases with a specific CaseType(1 in this case). If the box is checked, I would like the query to return only results where there is not a close date (Null). If the box is unchecked, I would like it to return all the cases with CaseType 1. My problem is I cannot seem to get an iiF statement to return "Is Null" if the argument is true. I tried using "Is Null" with quotes, without quotes, changing the control to all different types of variables etc. I just cannot get it the iif statement to return Is Null.

I have tried using IIf(Forms![FrmTherapyRef].[Form].OpenRefCheck= - 1, Is Null) in the Query Criteria.

There are several other queries and forms in my database that depend on figuring out if a case is open by having Null for the CloseDate. I've attached a screenshot of my query in design mode, and the SQL is posted below. COETherapy is just if someone has been assigned to the case, that is fine as it is to remain Is Null regardless of the control.

SELECT Demographic.FName, Demographic.LName, Demographic.DOB, CaseFiles.CaseOpen, CaseFiles.CaseClosed, CaseFiles.TherapCloseSum, CaseFiles.CaseID, Demographic.COEMR, CaseFiles.COETherapy
FROM Demographic INNER JOIN CaseFiles ON Demographic.COEMR = CaseFiles.COEMR
WHERE (((CaseFiles.CaseClosed)=IIf([Forms]![FrmTherapyRef].[Form].[OpenRefCheck]=-1,(CaseFiles.CaseClosed) Is Null)) AND ((CaseFiles.COETherapy) Is Null) AND ((CaseFiles.CaseType)=1));

Any help is greatly appreciate, oh Masters of Access! Please let me know if I need to clarify anything! I've never had to post to a board before because all you geniuses had already answered my questions! THANK YOU!



I’m here to help
Local time
Today, 06:53
Oct 29, 2018
Hi. Welcome to AWF!

Try it this way:
...WHERE CaseType=1 AND ((CaseClosed Is Null AND Forms!FrmTherapyRef.OpenRefCheck=True) OR Forms!FrmTherapyRef.OpenRefCheck=False)
Hope that helps...

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom