Select Open, Closed or All

[sbouley7]

I have a Work Order table containing "Date_Opened" and "Date_Closed" fields amongst other data. I have a query that populates a subform with data from the Work Orders table based on the value of an option group on the main form.
The query contains a calculated field Called "Open" that contains the following

 Open: IIf(IsNull([tbl_Work_Orders]![DATE_CLOSED]),1,2) 

to report the status of the work order. The "Open" calculated field also contains a criteria that checks the value of the option group in the parent form and Returns: Open Work Orders if the Option Group value is 1; Closed Work Orders if the value is 2; and all records if the value is anything other than 1 or 2. I’ve tried several different expressions and can get values 1 and 2 to work but cannot get it to return all records. The current expression is

 IIf(Forms![frm_Work_Order_Main]![optgrp_Show_Recs]=1, 1, IIf(Forms![frm_Work_Order_Main]![optgrp_Show_Recs]=2,2, “*”)) 

. 1 returns “Open” records as expected; 2 returns “Closed” records as expected; but 3 returns an error stating the expression is too complex.
Specific help would be greatly appreciated as my knowledge of expressions is limited.

Thank you!!!

[jforbes]

There are a couple ways to approach this and the best way would mostly depend on how you are using your second iif(). If you could post the SQL for the Query, we can knock this one out for you.

To get to the SQL of a Query while it's being edited in Access, click on Dropdown for View on the Ribbon and select SQL. You can then copy the text onto the clipboard and post it here.

[zmbd]

when you post the SQL here.
Please select the pasted text and use the [CODE/] format tool to properly paste the required formatting.
(See our FAQ section)

Also, sometimes it is easier to open the query in design mode, then right-click in a blank area of the table view pane and then select the SQL-View from the quick-menu.

[sbouley7]

jforbes and zmbd, thanks for your replies. I made a few attempts after my post so the SQL is a bit different but the ojective remains the same. With the following SQL statement, I get Open Work Orders when 1 is selected, Closed Work Orders when 2 is selected but nothing when 3 is selected.

SELECT tbl_Work_Orders.WO_ID, tbl_Work_Orders.TICKET_NUM, tbl_Work_Orders.TICKET_SYSTEM, tbl_Work_Orders.NETWORK, tbl_Work_Orders.SERVICE_ITEM_CAT_ID, tbl_Work_Orders.SERVICE_ITEM_SUB_CAT_ID, tbl_Work_Orders.User, IIf(IsNull([tbl_Hand_Receipts]![HR_Hldr_MI]),[tbl_Hand_Receipts]![HR_Hldr_LName] & ", " & [tbl_Hand_Receipts]![HR_Hldr_FName],[tbl_Hand_Receipts]![HR_Hldr_LName] & ", " & [tbl_Hand_Receipts]![HR_Hldr_FName] & " " & [tbl_Hand_Receipts]![HR_Hldr_MI] & ".") AS Customer, tbl_Work_Orders.REQUEST_DETAILS, tbl_Work_Orders.DATE_OPENED, tbl_Work_Orders.DATE_CLOSED, IIf(IsNull([tbl_Work_Orders]![DATE_CLOSED]),1,2) AS [Open]
FROM tbl_Hand_Receipts INNER JOIN tbl_Work_Orders ON tbl_Hand_Receipts.HR_Num = tbl_Work_Orders.USER
WHERE  (((IIf(IsNull([tbl_Work_Orders]![DATE_CLOSED]),1,2))=IIf([Forms]![frm_Work_Order_Main]![optgrp_Show_Recs]=1,1,IIf([Forms]![frm_Work_Order_Main]![optgrp_Show_Recs]=2,2,(IIf(IsNull([tbl_Work_Orders]![DATE_CLOSED]),1,2)) Like "*"))));
 

Thanks again!

[jforbes]

You could do something like this:

...
WHERE ([tbl_Work_Orders]![DATE_CLOSED] IS NULL AND [Forms]![frm_Work_Order_Main]![optgrp_Show_Recs]=1)
OR ([tbl_Work_Orders]![DATE_CLOSED] IS NOT NULL AND [Forms]![frm_Work_Order_Main]![optgrp_Show_Recs]=2)
OR [Forms]![frm_Work_Order_Main]![optgrp_Show_Recs]>2

You may need to tweak it a little, but the idea it to break it up into smaller pieces and OR the conditions together. The last part of the OR evaluates to true and returns all the records when that Option is selected on the Form.

[NeoPa]

You're after the following Truth Table if I understand you correctly :

   Record       Option          Result
True (Open)     1 (Open)        True
True (Open)     2 (Closed)      False
True (Open)     X (Either)      True
False (Closed)  1 (Open)        False
False (Closed)  2 (Closed)      True
False (Closed)  X (Either)      True

I believe the following should work for you efficiently :

WHERE   ([Forms]![frm_Work_Order_Main]![optgrp_Show_Recs] Not In(1,2))
   OR   IsNull([tbl_Work_Orders]![DATE_CLOSED])<>([Forms]![frm_Work_Order_Main]![optgrp_Show_Recs]=1)

One of the benefits of this approach is that the whole of the first line can be worked out locally and entered into the SQL as a constant, and so can the second part of the second line (After <>).

[sbouley7]

jforbes and NeoPa,

Thank you both for your replies!!!

I tried both of your recommendations and jforbes work great without any tweaking required whereas NeoPa's recommendations probably needed some tweaking.

jforbes, I understand the logic in your Where statement but would not have thought to approach it like that. So, thank you for the education.

[NeoPa]

You're right. I've just noticed that I used the reference you gave in your first post, which is faulty. Any reference to Forms! should be written as [Forms]! from SQL.

I've updated my earlier post to make it easier to test from.