Toggle a criteria on and off?

[overlords]

Hey there. First post.

Here goes.

I have a continuous form that displays records that are fed to it with a simple select query. A few of the fields need to have criteria that is chosen by the user.

So, for example: On this form a user can pull down their favorite comic book title from a combo box, say : amazing spider man. The continous form now only displays the comics that belong to this title. There is another combo box where you can choose the format of the book you'd like to be displays, so lets say: comic, trade paperback, squarebound etc.

This is all working quite well so far, but I'm running into a problem where I'd like to toggle these criteria on and off. So, for example, if someone would like to see all the amazing spiderman books regardless of 'format', i'd like there to be a checkbox he can uncheck to effectively 'turn off' the format criteria and thus return all ASM books. I would like to repeat this 'toggle' check box for every combo or list box I am providing to the user to further filter the results.

I've done my best messing around with the query builder, and I generally only ask questions in help forums when I'm really desperate, so if anyone out there can help out with a solution I'd be eternally grateful.



thanks.

[June7]

Bound controls cannot be used to enter filter criteria.

For one method to filter form, review http://datapigtechnologies.com/flash...tomfilter.html

[overlords]

Bound controls cannot be used to enter filter criteria.

For one method to filter form, review http://datapigtechnologies.com/flash...tomfilter.html

Sorry, Perhaps I was unclear. My form is a continous form and my combo boxes are set up just like in that video (unbound). Essentially, the 'filtering' aspect of my form is done, what I really need help with is toggling these filters on and off with the help of another control on the form.

[Rawb]

I prefer to do this by using VBA to control the .Filter and .FilterOn Properties of the Form instead of building the criteria into the Query itself.

That said, you can do what you want by wrapping your current condition in an IIf() Statement:

Like IIf([Forms]![MyForm]![chkFilterBookType]=True,[Forms]![MyForm]![cboBookType] & "*","*")

In this example, chkFilterBookType is a Checkbox Control that enables or disables filtering by book type and cboBookType is the Combo Box Control listing the book types you can filter by.

Adding the IIf() Statement allows you to change the condition based on the value of chkFilterBookType. If it's checked (True), then the condition Filters on the condition Like [Forms]![MyForm]![cboBookType] & "*" (which only includes items beginning with the value of your Combo Box Control).

If it's not checked (False), then it instead, Filters on the condition Like "*" (which would include everything).

[June7]

That DataPig tutorial shows a button to 'clear filter'.

[overlords]

I prefer to do this by using VBA to control the .Filter and .FilterOn Properties of the Form instead of building the criteria into the Query itself.

That said, you can do what you want by wrapping your current condition in an IIf() Statement:

Like IIf([Forms]![MyForm]![chkFilterBookType]=True,[Forms]![MyForm]![cboBookType] & "*","*")

In this example, chkFilterBookType is a Checkbox Control that enables or disables filtering by book type and cboBookType is the Combo Box Control listing the book types you can filter by.

Adding the IIf() Statement allows you to change the condition based on the value of chkFilterBookType. If it's checked (True), then the condition Filters on the condition Like [Forms]![MyForm]![cboBookType] & "*" (which only includes items beginning with the value of your Combo Box Control).

If it's not checked (False), then it instead, Filters on the condition Like "*" (which would include everything).

Thanks to you both for helping me with this problem. That's exactly what I needed.

I just had two questions in regards to this post. Will ' like "*" ' also return nulls?

And are there any advantages to doing this through vba using the .filter and .filterOn properties?

[Rawb]

Yes. An asterisk will match "zero or more characters" so it will grab Null values as well. If you want to ignore Records with Null values, you will need to do a check for the length of the Field instead.

IIf([Forms]![MyForm]![chkFilterBookType]=True,"Like [Forms]![MyForm]![cboBookType] & "*",Len([MyField]) > 0)

Where [MyField] is the name of the Field in your Query itself.

[June7]

No, I don't think Like will return records where field is null. Null is not a zero length string. Try applying a Like "*" criteria filter to a single field that has records with null. Are those records retrieved?

I avoid dynamic parameterized query because sometimes as developer I want to open query and don't want to enter filter criteria. I would use a VBA method. The Filter and FilterOn approach is one.

[overlords]

It doesn't. Still struggling with returning null values when I'd like them to pop up. I've tried this in the criteria row of my quantity field

IIf([Forms]![frm_productBrowser]![QuantityFilter]=1,Is Null,0)

And whenever I close the query builder to save it, it turns it into this:

IIf([Forms]![frm_productBrowser]![QuantityFilter]=1,([tbl_items].[Quantity]) Is Null,0)

Either way it doesn't work. Criteria really does not seem to like seeing 'is null' in a conditional statement like iif().

[June7]

Need to handle the null. Create a field in with an expression like: Nz([fieldname],""). Apply LIKE operator filter criteria to that constructed field.

[overlords]

Sorry to be a bother, but I don't quite understand what yo mean by the second part of your last post, June7. I constructed a new field in my query builder with the expression

Nz([Quantity],"")

Not quite sure what to do next.

[overlords]

Ah, my apologies. I figured it out. I was confused because of my inexperience with the NZ function. My thanks, june7. Rep'd.