Microsoft access query with IIf
<p>I have a combo box with 3 values: "Yes, No, *".  * is intended to represent both yes and no options.</p>

<p>In a query within the criteria box of a field called service. CompletedOn I have placed the below IIf statement:</p>

<p>IIf([Forms]![serviceReportForm]![workcompletedDropdown]="Yes",([service].[CompletedOn]) Is Not Null,([service].[CompletedOn]) Is Null)</p>

<p>I am trying to ensure that when yes is selected in the combobox any record which contains a completed on value is shown. If no is selected it is not shown and if * is selected all records are shown. Currently the code is showing no records at all. Could you please advise?</p>

<p>SQL below</p>

<pre><code>SELECT customer.CustomerID,
 customer.[Customer Name],
 Equipment.Description,
 Equipment.PurchaseID,
 service.Date,
 service.Fault,
 service.JobNo,
 service.costOfRepair,
 service.ServiceID,
 service.CompletedOn,
 CalloutUrgency.CalloutDescription,
 CalloutUrgency.CalloutUrgencyID,
 service.AgentID,
 agent.Serviceagentname,
 supplier.supplierID,
 supplier.supplierName,
 Equipment.ManufacturerID,
 Manufacturer.[Manufacturer Name]
FROM Manufacturer 
INNER JOIN ((customer 
INNER JOIN (supplier 
INNER JOIN Equipment 
    ON supplier.supplierID = Equipment.SupplierID) 
    ON customer.CustomerID = Equipment.CustomerID) 
INNER JOIN (agent 
INNER JOIN (CalloutUrgency 
INNER JOIN service 
    ON CalloutUrgency.CalloutUrgencyID = service.CalloutUrgencyID) 
    ON agent.AgentID = service.AgentID) 
    ON Equipment.EquipmentID = service.EquipmentID) 
    ON Manufacturer.ManufacturerID = Equipment.ManufacturerID
WHERE (((customer.[Customer Name]) Like [Forms]![ServiceReportForm]![houseDropdown]) 
AND ((service.Date) Between [Forms]![ServiceReportForm].[fromDate] And [Forms]![ServiceReportForm].[toDate]) 
AND ((service.CompletedOn)=IIf([Forms]![ServiceReportForm]![workcompletedDropdown]="Yes",Not Null,Null)) 
AND ((CalloutUrgency.CalloutDescription) Like [Forms]![ServiceReportForm]![urgencyDropdown]) 
AND ((agent.Serviceagentname) Like [Forms]![ServiceReportForm]![serviceagentDropdown]) 
AND ((supplier.supplierName) Like [Forms]![ServiceReportForm]![supplierDropdown])) 
    OR (((service.Date) Between [Forms]![ServiceReportForm].[fromDate] And [Forms]![ServiceReportForm].[toDate]) 
    AND (([Forms]![ServiceReportForm])=" All"));
</code></pre>