Filter by Combo box entry

[ms1202]

Hi,

I have a form with a list box that is filtered by the entry made into a combo box. It works fine except that I want an option which shows all records in the list box. I tried using the formula below in the query criteria for the list box but it returns no records when I select All in the combo box. Why won't it work? Is there a better way to do this? Can anyone help?

IIf([Combo32].[Value]="All",Like "*",[Combo32].[Value])

 

[badermsb]

Try Forms![Form Name]![ComboBox]

 

[ms1202]

Sorry, no joy. All of the other filters work fine, it's just this All option that isn't. There is an All entry in the field it's looking in too so it isn't just evaluating the IIf as false all the time because it doesn't show that All. It's almost like it's looking for a * character rather than using it as a wildcard character.

 

[EMP]

Try setting the criteria for the field in a new column in the query grid like the following (replacing with the correct field name):-

------------------------
Field: IIf([Combo32].[Value]="All", True, [FieldName]=[Combo32].[Value])

Show: uncheck

Criteria: True
------------------------

See Jon K's thread for using True in an IIF expression to return all records:
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[ms1202]

Perfect, thank you.