Filtering 'All' from Combo box Value List

[Chris115]

Hi
I have a combo box on a form which has a row source type of Value List in which I've manually typed in 2 entries: Owned, Leased.

I have a query which filters on the results of the combobox, and works fine for when I select either option Owned or Leased.

Is there an easy way to filter all (both Owned & Leased)? I've tried leaving the combobox blank, and tried entering a * in the value list, but they both return no results.

SELECT Assets.*
FROM Assets
WHERE (((Assets.Possession)=[Forms]![Home]![Combo56]));

 

[pr2-eugin]

If you are using a Wildcard entry.. Your SQL should be..

SELECT Assets.*
FROM Assets
WHERE (((Assets.Possession) [COLOR=Red][B]LIKE IIF([Forms]![Home]![Combo56] = "All", "*", [Forms]![Home]![Combo56][/B][B])[/B][/COLOR]));

Then change your Combo List to.. Leased, Owned, All

 

[Chris115]

Thank you, that's worked perfectly!

 

[pr2-eugin]

Glad to have helped.. Good Luck..

 

[Brianwarnock]

No point in using"Like" if you are not doing partial string searches.
Like cannot use indexes a,normally, and will not return Nulls.

SELECT Assets.*
FROM Assets
WHERE ((Assets.Possession) =[Forms]![Home]![Combo56] or [Forms]![Home]![Combo56] = "ALL")

Is what you need once ALL is added to the combo, you could use Is Null and leave the combo blank

Brian