Use a tick box in a query

[phil123]

Hi,

I'm wanting to a run a query where one of the fields is saved as a yes/no. This basically records whether an action has been completed or not.



I therefore want the database to retrieve the records where the box is left un-ticked meaning the action is still outstanding. However, I want this part of the query to be answered on a form. I.e. there will be a tick box on a form which says 'tick if you want to retrieve outstanding actions'. Therefore if this tick box is set to true, then the query needs to retrieve the records where the tick box on the table is left blank (i.e. the actions are still outstanding).

I'm therefore looking for a bit of code which I need to put in my query which looks up the value from the form and performs the relevant action based on whether it is set to true or false.

Thanks,

Phil

[Micron]

The problem could be the disconnect between the values, i.e. the form value is Yes/True but the table field value being sought is No/False. You might want to reverse the logic on the form so that these are in sync to make it simpler. If not, I guess you're looking at a long drawn out IIF expression in the query so that if form is yes, criteria is no and vice versa. I presume the form checkbox will not be bound? Not sure, but I don't think the odd behaviour of checkboxes (when unbound or on a new record, they have one colour if yes, another if no, and another if it's unknown - a triple state) will come into play. Your query may not play well with this if it isn't addressed in the IIF expression.

A code solution would be the most reliable. I didn't offer one because your question was how to do this in a query.

[CJ_London]

where the tick box on the table is left blank

either means the field in set to no/false, or is null. Generally much better to set the display control to textbox so you can see what you actually have: -1 for True/Yes, 0 for No/False or null - no choice made

so your criteria for this field will be

WHERE (nz(tickboxfield,0) <> forms!myform!tickboxcontrolname) OR nz(forms!myform!tickboxcontrolname,0)=0

[June7]

I don't use dynamic parameterized queries but with an UNBOUND checkbox, query like:

SELECT * FROM table WHERE [Completed] = Not Forms!formname!checkbox;

Set the UNBOUND checkbox TripleState property to No and DefaultValue to -1.

However, this means the form retrieves either completed or not completed, not all.

Wouldn't it be more informative to use a Date/Time field to indicate completed? If field Is Null it is not complete.

Review http://allenbrowne.com/NoYesNo.html

[phil123]

Hi guys,

Thanks very much for the advise here. Really useful.

So I've decided to rid of the tick box and just use the date completed field and have set up a query which returns if value Is Null. Sorted.

However, as I have more than one task under a given record, if the first date in the list is null, then another record won't retrieve as this bit of code has already blanked this record out.

So, I was wondering whether you can suggest a bit of code for the criteria which says Is Null or retrieve if x field is Null, or if y field is Null, etc....

Many thanks,

Phil

[June7]

As I said, I don't use dynamic parameterized queries. I prefer VBA to build filter criteria. Review http://allenbrowne.com/ser-62.html

[phil123]

Hi June7,

Thanks for the advice - I've had a read through the review you have posted and this seems to make much more sense!

However, is it possible to have a subform which collects the required pieces of information as in the 'Header' in this example, and then using a command button it applies this criteria to open up a new form and populate it with the results?

If so, could you give a bit of example coding which I could adapt for the VBA?

Thanks

Phil

[June7]

Yes, Allen's article (see Conclusion) indicates the code to construct criteria can be used to filter forms and reports. Apply the criteria to WHERE argument of OpenForm or OpenReport methods. Do some research on coding these methods. Many examples.