iif statement in criteria to select all

[BroncoMarc]

I've got a combo box in a form that uses a query to display a table in a sub form. One of the option in the combo box is all. I'm trying to write an if statement in the criteria for the query so that it either show what is a match for what is selected in the combo box, or it shows all if all is selected.

This is basically what I'm doing:
IIf([Forms]![Product Catalog]![cboSegment]<>"all",[Forms]![Product Catalog]![cboSegment],"*")

it works for everything I select except or 'all'. I've tried *, like * and a few other things. If I select 'all' the query returns nothing.

What do I need to put in there??

Thanks,
- Marc

 

[Rob.Mills]

Do it like this:

First criteria line: [Forms]![Product Catalog]![cboSegment]

Second criteria line: [Forms]![Product Catalog]![cboSegment] Is Null

If the combobox is null it will return all records. If not it will filter.

 

[BroncoMarc]

I tried that and it works when the form first comes up and the combo box is blank, but there is no way to select a blank in the combo box again once something else is chosen. The combo box is eventually going to be set to default to 'all'.

Also, the 2nd line in the criteria disappears everytime I reopen the query. Maybe access thinks I don't need it?

- Marc

 

[Rob.Mills]

It doesn't actually disappear, access just changes it slightly. Open it in design view and look at the last column. You'll see how it changed.

As for the value in the combobox. Create a button next to it that says 'Remove Filter'. Program it to set the combobox to null and requery the subform.

 

[Jon K]

Try this setting in a column in the query grid (replacing with the correct field name):-

Field: IIf([Forms]![Product Catalog]![cboSegment]<>"all", [FieldName]=[Forms]![Product Catalog]![cboSegment], True)

Show: uncheck

Criteria: <>False

 

[Rob.Mills]

You trying to make me look bad, Jon??

My idea works too...

 

[Jon K]

Sorry, Rob. Never thought of that. Just wanted to offer a solution for the option "all".

 

[Rob.Mills]

Ya, I know. Just teasing.

 

[BroncoMarc]

Thanks for the suggestions Rob, but I do want it to work when selecting all.

Jon, That worked! Took me a second to figure out what you were doing, but I got it to work. I'm going to have about 10 combo boxes that update the query, so that's going to be a long query. I've got 8 of them working so far.

Isn't there a way to do it in an if statement like I was doing originally?? ie - if it is not set to all, show what matches the selection, otherwise show everything.

Thanks to both of you for the suggestions..

- Marc

 

[Jon K]

>
Isn't there a way to do it in an if statement like I was doing originally?? ie - if it is not set to all, show what matches the selection, otherwise show everything.
<


When you put an IIf statement in the Criteria cell, Access will add an invisible equal sign in front of the statement. So the whole criteria expression will become:

[FieldName]=IIf([Forms]![Product Catalog]![cboSegment]<>"all", [Forms]![Product Catalog]![cboSegment],"*")

Hence when the combo box is left blank or equal to "all", the expression will evaluate to [FieldName]="*". As you do not have an * in the field, no record will be returned.


The expression [FieldName]=[FieldName] will return every record that has a value in the field. So if the field do not contain any null values (or if you do not want to return null values), you can simply replace the "*" with [FieldName] in your original statement to make it evaluate to [FieldName]=[FieldName].

Alternatively, you can put the Like operator in front of your original IIf statement, thereby keeping the "*" and avoiding the invisible equal sign. But still [FieldName] Like "*" will exclude any null values.

The True in my statement returns everything, even null values.

So you can choose whichever method that suits your needs.