Access checkbox on form to apply query criteria 1

[jonfurn]

Hi all -

Any help on this much appreciated - I've searched far and wide, but just haven't found the answer that I can apply easily to my database!

i) I have a pre-existing query that pulls various data from different tables;
ii) I now need to, retrospectively, add a checkbox to the main form to alter this query depending upon whether otr not it's checked;
iii) If the box is checked, I want it to run the query as normal (as it currently stands), but if it's unchecked, I want the additional criteria of >10000 to apply to a field in the query called SOURCE (this is basically determining whether the data comes from us or from external places - if the SOURCE is <10000 it'll be our data, if it's >10000 it'll be external. I just want the option of showing everything or only ours (<10000)

In an ideal world, I don't want to muck around with VBA and AfterUpdate stuff.

Is there a simple way in the SQL of the query (it's a Union query, so it's gonna have to be in SQL!), to apply this criteria to the SOURCE field depending on whether or not the box is checked?

Your help would be appreciated.
All the best,
Jonathan

 

[PHV]

WHERE ...
AND (SOURCE>10000 OR [Forms}![yourMainForm]![yourCheckBox}=True)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jonfurn]

Thanks, PHV.

That looks similar to other answers that I found online during my trawl. The Union query is huge and I'm try to check that it works simply first in a dummy db. Is there something I can write within the Criteria row in the Design view of the query that will do the same thing?

Thanks again for your response.

J

 

[jonfurn]

For instance, ideally I'd like something like this to work:

(This is entered in the Criteria row of the SOURCE column)

IIf([Forms]![FrontEnd]![checkbox]=-1,<10000,"*")

J

 

[PHV]

<10000 OR Nz([Forms]![FrontEnd]![checkbox],0)=0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jonfurn]

Thanks loads, PHV. Exactly what I needed. To see it work for the first time after so many failed attempts was a beautiful thing.

Thanks again.
Jonathan