Checkbox controlled criteria

[Kevin C Niven]

I wonder if someone can help. Either I don't seem to be thinking very
logically this morning or I'm using incorrect syntax.

I currently have a query with two fields, tblfoo.foo and tblbar.bar.
I would like to create criteria such that:

When Forms!MyForm!chkBox is True, I want to show only those
records where tblbar.bar is Not Null.

When Forms!MyForm!chkBox is False, I want to show only those
records where tblfoo.foo is Not Null.

Could someone write the criteria (or a WHERE) so that the above
happens?

Many TIA!
Kevin

 

[Duane Hookom]

It is really difficult to understand the business requirement for something
like this.

I would create a union query [quniFooBar] like:

SELECT foo as Fld, "foo" as tbl
FROM tblFoo
UNION ALL
SELECT bar, "bar"
FROM tblbar;

Then, you can create a query based on the union query
SELECT quniFooBar.*
FROM quniFooBar
WHERE tbl = IIf(Forms!MyForm!chkBox = True,"bar", "foo")
AND Fld Is Not Null;

 

[John Spencer MVP]

Something like this should work - if I understand what you want and have made
a reasonable guess about your query.

SELECT *
FROM YourQuery
WHERE (tblBar.Bar is Not Null AND Forms!MyForm!chkBox)
OR (tblFoo.Foo is Not Null and Forms!MyForm!chkBox = False)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County