Hi,

I have a query that calls on two tables and returns a list of Reference Numbers from Table A. The query is set so that it won't return reference numbers that also appear in Table B - ie. I've joined on the reference number in the two tables and put a simple "Is Null" criteria against the Table B version.

However, what I'd like to do is flip this query in three ways:
1) as it is - bring back records in Table A if they are not in Table B
2) reverse - bring back records in Table A only if they are in Table B
3) no filter - bring back all records in Table A regardless of whether they're in Table B or not. (though this option is less of a priority)

I'd like to do this from criteria in a form and I wrote this in to the criteria under the Reference Number field from Table B:

IIf([Forms]![frmForm1]![Toggle1]=-1,"Is Null","Is Not Null")
and I also tried:
IIf([Forms]![frmForm1]![Toggle1]=-1,"Is Null","*")

I now know (and had a strong feeling before even writing it!) that the syntax here in both cases is horribly inaccurate and this doesn't work, but perhaps they describe more succinctly what it is I'm trying to do?

Many thanks in advance for any help that can be provided!

Have a search on the forum for "Search form" and see if you can figure out how that works... This is much more flexible and such things than your proposed method which WILL lead you into trouble.

Hi,

Thanks for that. Your comment inspired me to re-think it and approach it in a different way:

I've created another field called "Exclude" and put this expression:
Exclude: IIf(IsNull([ReferenceNumber]),0,1)

Then in the criteria for that field I've put:
IIf([Forms]![frmForm1]![Toggle1]=-1,1,0)

I'm sure I'll get the third of my requirements via a similar route.

Thank you!

Still this is not 'the best way' to do this... do the search and learn!