Fine Tuning parameter query and need help please

[heather001]

I have a parameter query that was discussed a while back Here where I needed help with a nested IIF statement returning multiple values. This has been working great until I was asked to add a couple more processes to the list. Now I get an error saying expression too complex. Ok, well I agree. So I decided to look into calling a select case function from the query to perform this task. In the original discussion about this query, it was discussed, but I was already satisfied with the IIF statement and moved on. I'm a complete newbie when it comes to passing a parameter to a function in order to return the records I would like in my query. Please help me get my head around this.

I'm ready to rethink this whole parameter query and would appreciate some input on using a case statement. Here is the sql for this query:

SELECT tblInspections.InspectedQty, tblInspections.RejectedQty, tblInspections.AcceptedQty, tblInspections.InspectDate, (DatePart("ww",[InspectDate],2)-1) AS Week, tblCustImport.CustomerID, tblInspections.InspectID

FROM tblWorkCenters INNER JOIN (tblProcess INNER JOIN ((tblCustImport INNER JOIN tblMaxImport ON tblCustImport.CustomerID = tblMaxImport.[Customer Number]) INNER JOIN (tblInspections INNER JOIN tblInspectDetail ON tblInspections.InspectID = tblInspectDetail.InspectID) ON tblMaxImport.[WO#] = tblInspections.[WO#]) ON tblProcess.ProcessID = tblInspections.ProcessID) ON tblWorkCenters.WorkCenterID = tblInspectDetail.WorkCenterID

WHERE (((tblInspections.InspectDate) Between [Forms]![frmRejectopt]![txtStartDate] And [Forms]![frmRejectopt]![txtEndDate]) AND IIf([Forms]![frmRejectopt]![optWC2]=1,[tblWorkCenters]![WorkCenterID] = ("S"),
IIf([Forms]![frmRejectopt]![optWC2]=2,[tblWorkCenters]![WorkCenterID] = ("T"),
IIf([Forms]![frmRejectopt]![optWC2]=3,[tblWorkCenters]![WorkCenterID] = ("M"),
[tblWorkCenters]![WorkCenterID])))
AND
IIf([Forms]![frmRejectopt]![optgrpWC]=2,[tblProcess]![ProcessID] In (1),
IIf([Forms]![frmRejectopt]![optgrpWC]=3,[tblProcess]![ProcessID] In (2),
IIf([Forms]![frmRejectopt]![optgrpWC]=4,[tblProcess]![ProcessID] In (5),
IIf([Forms]![frmRejectopt]![optgrpWC]=5,[tblProcess]![ProcessID] In (9),
IIf([Forms]![frmRejectopt]![optgrpWC]=6,[tblProcess]![ProcessID] In (10),
IIf([Forms]![frmRejectopt]![optgrpWC]=7,[tblProcess]![ProcessID] In (11),
IIf([Forms]![frmRejectopt]![optgrpWC]=12,[tblProcess]![ProcessID] In (4),
IIf([Forms]![frmRejectopt]![optgrpWC]=8,[tblProcess]![ProcessID] In (3),
IIf([Forms]![frmRejectopt]![optgrpWC]=9,[tblProcess]![ProcessID] In (6),
IIf([Forms]![frmRejectopt]![optgrpWC]=11,[tblProcess]![ProcessID] In (7),
IIf([Forms]![frmRejectopt]![optgrpWC]=10,[tblProcess]![ProcessID] In (8),
IIf([Forms]![frmRejectopt]![optgrpWC]=13,[tblProcess]![ProcessID] In (1,9,10,11),
[tblProcess]![ProcessID])))))))))))))
GROUP BY tblInspections.InspectedQty, tblInspections.RejectedQty, tblInspections.AcceptedQty, tblInspections.InspectDate, (DatePart("ww",[InspectDate],2)-1), tblCustImport.CustomerID, tblInspections.InspectID
ORDER BY tblInspections.InspectDate, (DatePart("ww",[InspectDate],2)-1);

TIA!!

 

[grnzbra]

Create your function in a module (although it might also work on a forme - I had done it with a module). Then set the function in the criteria.

You might also consider using vba and the select case statement to control a sql statement and use that to modify the query definition.

 

[heather001]

I took a closer look at the link I posted and was able to understand the logic and make it work to my liking. I did use a function as you recommended and it worked perfectly. I'm really liking the way functions make complicated queries a lot cleaner.