Microsoft access query with IIf

Question

I have a combo box with 3 values: "Yes, No, *". * is intended to represent both yes and no options.

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

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

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?

SQL below

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"));

Can you show your full query? It looks like you are trying to make dynamic sql but iif can't be used like that — Brad
Surely there is more to this query than what you show. There's a table and columns somewhere and a full where clause. Putting that in the question will be very helpful. — Brad
Is there a way I can print this in a readable format? Ie, a single statement? At the moment I'm looking at query editor and there are lots of boxes with different fields and bits of code in them so it's very difficult to show the whole query — Simon
you can change your view from Design View to SQL view in the top left of the ribbon. Or Click SQL in the bottom left of the whole window. — Brad

Brad Brad · Accepted Answer · 2016-06-07T20:19:36

You're treating the IIF function as though it were returning a string which is then inserted into your query and the query then executed. However, that's not the order in which things are processed. IIF should return either a value to check against or a boolean itself. replace this

((service.CompletedOn)=IIf([Forms]![ServiceReportForm]![workcompletedDropdown]="Yes",Not Null,Null))

with this

IIf([Forms]![ServiceReportForm]![workcompletedDropdown]="Yes",service.CompletedOn is Not Null,service.CompletedOn is Null)

Notice too that I got rid of the = because you can't test for null using =, you must use is.

To return All records when * is selected

IIF([Forms]![ServiceReportForm]![workcompletedDropdown] = "*"
  ,True
  , IIf([Forms]![ServiceReportForm]![workcompletedDropdown]="Yes"
    ,service.CompletedOn is Not Null
    ,service.CompletedOn is Null))

Microsoft access query with IIf

2 Answers