Help a noob understand IIF

[rbennion]

Hey all,



I am quite familiar with CASE, much to my dismay I have discovered Access doesn't support CASE. So, I have been referred to IIF statements instead. I actually have my query working, I just want to understand how its working. lol.

All I want to do is only populate the "Enrollment_Date" field if there is a value, if its null I don't want it. Below is the code of how I have done this (bolded and underlined).

SELECT DatePart ("yyyy", [Referral_Date]) AS [Year], DatePart ("m", [Referral_Date]) AS [Month],
Count(IIf(Enrollment_Date IS NULL,1)) AS NoShowPatients, Count(General.Referral_Date) AS ReferredPatients, Round([NoShowPatients]/[ReferredPatients],2) AS NoShowRate
FROM [General]
GROUP BY DatePart ("yyyy", [Referral_Date]), DatePart ("m", [Referral_Date]);

Is Access interpreting the "1" in the "value if true" criteria as a literal 1 so it adds them up to give me my COUNT? And why am I able to leave the "value if false" empty entirely?

Thanks for any response, I know this is probably rudimentary.

[ranman256]

access DOES supports CASE in vb, queries do not.
in queries you can use IIF, or SWITCH, or use a lookup table.

but you shouldnt use IIF in a count. Put the iif in a query, THEN count them in another query.

[June7]

Seems the false argument of IIf in query defaults to Null. Nulls are not aggregated. Do a test by putting a value (any value - 5, 'a', 'None') in the argument and see what happens, then use Null. Can also put any value in the true argument and the output will be the same as using 1.

An alternative is: Sum(IIf(Enrollment_Date Is Null, 1, 0))

or if you don't even want 0 to return: Sum(IIf(Enrollment_Date Is Null, 1)) - again the false argument defaults to Null and gives the same output as explicitly referencing Null.

VBA appears to require the false argument to be explicit but an expression in textbox managed without.