Controlling a WHERE with a checkbox

[John Harrington]

I have a query like:

SELECT tbl1.fiel, tbl1.field
FROM ((tbl1
LEFT JOIN tbl3 ON tbl1.coID = tbl3.ID)
LEFT JOIN tbl2 ON tbl1.field= tbl2.field)
LEFT JOIN tbl4 ON tbl3.ID = tbl4.field
WHERE ((tbl4.field) Is Null)

I would like to do the following.

When Forms!Form1.checkbox is checked,
then the WHERE clause is applied

When Forms!Form1.checkbox is NOT checked,
then there is no WHERE restriction

How do I rewrite the SQL to do that?

Thank you,
John

 

[John Spencer]

SELECT tbl1.fiel, tbl1.field
FROM ((tbl1
LEFT JOIN tbl3 ON tbl1.coID = tbl3.ID)
LEFT JOIN tbl2 ON tbl1.field= tbl2.field)
LEFT JOIN tbl4 ON tbl3.ID = tbl4.field
WHERE ((tbl4.field) Is Null) OR Forms!Form1.checkbox=False

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

 

[Dorian]

You could just add an AND clasue and check for the Checkbox being true
e.g.
AND Forms!Form1.checkbox = True
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[KARL DEWEY]

I believe it should read like this --
WHERE ((tbl4.field Is Null) AND Forms!Form1.checkbox = True) OR
Forms!Form1.checkbox = False

 

[Armen Stein]

I would like to do the following.

When Forms!Form1.checkbox is checked,
then the WHERE clause is applied

When Forms!Form1.checkbox is NOT checked,
then there is no WHERE restriction

How do I rewrite the SQL to do that?

Hi John,

Others have responded with ideas on incorporating your form control
directly into your query. However, if you want to actually modify the
Where clause of a SQL statement in VBA code, so that it's independent
of the form, read on...

We've written code to automatically find and replace the Where clause
in a SQL statement. It's a free download called "J Street SQL Tools"
on our J Street Downloads page at http://ow.ly/P21j.

Take the text and paste it into a new module called
basJStreetSQLTools.

To use it, try the function ReplaceWhereClause. You send in a whole
SQL statement and the new desired Where clause, and it locates and
snips out the old one, inserts your new one, and gives you back the
new statement. If you send in a null or empty Where clause, the
function just removes any existing one from the statement.

Rebuilding a whole SQL Statement in code is laborious and often leads
to hard-to-diagnose errors. Our replacement technique allows you to
store the whole query structure in a property or saved query without
having to rebuild it each time in code. All you have to do is rebuild
the Where clause. It's one of the most useful functions we've ever
written - it's used in every single Access application we build. It
works with both Access and SQL Server query syntax, so it works fine
on passthrough queries too.

By the way, there's also a ReplaceOrderByClause function that does the
same for sorting.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John Harrington]

My thanks to all of you for your responses.