Filter Query Criteria via Textbox in a form

[r32chaos]

I am displaying a list box in a form (Requests), this list box contains all records. I have a combobox which contains 3 choices; All, Open, Completed. After a selection in the combobox, a textbox (statusFilterA) is populated as follows: All = "*", Open = <> "Completed", Completed = "Completed".

StatusFilterA unbound textbox contains: =IIf([statusfilter]="All","*",IIf([statusfilter]="Open","<>""Completed""",IIf([statusfilter]="Completed","Completed")))

A query field contains the following criteria: Like "*" & [Forms]![Requests]![StatusFilterA] & "*"

Problem is, "All" and "Completed" works fine but the "Open" choice does not work.

For the time being, I made a 2nd Listbox which is made visible whenever "Open" is selected and this Listbox runs off of a new Query which contains the criteria Not Like "Completed". Of course the other Listbox, which can display "*" and "Completed" is made invisible.

So, every time a choice is made in the combobox, it either hides the first listbox and displays the 2nd, or visa versa.

I wish there was a way to accomplish the exclusion of "Completed" records when "Open" is selected. I tried many different methods and nothing worked.

Any help will be very much appreciated.

Regards

[June7]

It doesn't work because <> operator results in bad syntax. The query ends up with two operators (LIKE, <>).

Try:
=IIf([statusfilter]="Open", "[!C]", IIf([statusfilter]="Completed", "Completed", ""))

Don't use the first wildcard.

LIKE [Forms]![Requests]![StatusFilterA] & "*"


I never use dynamic parameterized query. I use VBA to construct filter criteria and set form Filter and FilterOn properties or pass criteria to form or report when opening.

[r32chaos]

It doesn't work because <> operator results in bad syntax. The query ends up with two operators (LIKE, <>).

Try:
=IIf([statusfilter]="Open", "[!C]", IIf([statusfilter]="Completed", "Completed", ""))

Don't use the first wildcard.

LIKE [Forms]![Requests]![StatusFilterA] & "*"


I never use dynamic parameterized query. I use VBA to construct filter criteria and set form Filter and FilterOn properties or pass criteria to form or report when opening.

Thank you!! This worked perfectly!
And you are right; I need to be using VBA to construct filter criteria etc. Is there any particular VBA book you would recommend? I really need to get a grasp on it.

Again, thank you!!

Kind Regards

[June7]

I don't have any books dedicated to programming in Access. This one sounds interesting: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc. I expect there is a later edition.