Unfilter a query using a custom list box value

[Docho]

Hi,

I've been trying to solve this problem for almost a full day now and i'm starting to get desperate.
Can't seem to find any help on the net, so i hope maybe one of you will be able to save me.

I have 2 list boxes :
1. MainBox - shows a list of records from one table.
2. FilterBox - this is actually a query that shows a list of records from another table, filtered by the record i choose in the MainBox.

I added the MainBox a custom line displaying "All" by adding this code to its RowSource :
UNION SELECT "(All)" from MainTbl
So now i see the list of records like before, with a "(All)" as the 1st line.

Now i want to set up the FilterBox query so that if i select the "(All)" line, it will not filter the table and just show me all records, so i entered this code as the appropriate column condition :
IIf(([Forms]![frmTest]![MainBox])="(All)",([tblFilter]![FilterColumn]) Is Not Null,[Forms]![frmTest]![MainBox])

That's supposed to check if i have selected the "(All)" line in the MainBox.
If i didn't - then the FilterBox gets filtered by the MainBox value - this is working fine.
If i did - then the column filter value should become "Is Not Null" which, according to my understanding, means that the column won't get filtered - that's not working! When i select the "(All)" line, i just don't get any records in the FilterBox.

Does anyone have any idea what i can do to fix this ?

Sorry for the long explanation.
Thank you very much!

 

[antomack]

I take it that your iif is on the criteria line of your query, if so then you should change it to the following

Like IIf(([Forms]![frmTest]![MainBox])="(All)","*",[Forms]![frmTest]![MainBox])

This will apply a filter of all records to be shown when you select (All) from your list.

 

[RV]

Use a WHERE clause in the control source of the list box FilterBox:

WHERE [Forms]![frmTest]![MainBox])="(All)"
OR [tblFilter]![FilterColumn] = [Forms]![frmTest]![MainBox]

RV

 

[Docho]

Thanks!

You did it!
Seems like i just needed to add "like" at the beggining of the critiria!

I will go and learn about that WHERE clause too.

Thank you very very much! This has been a major headache for me.