query using combo box value

[Guest]

Hi,

I have a database with a "Company" table. A combo box on a "company" form
lists the company names. A lookup query filters the table according to the
selection in the combo box. I want to have an option in the combo box that
will list all the companies in the table rather than only one.. such as an
"Any" or "All" option. How can I do this?

I have tried passing the combo box value to a variable in an If statement
checking for the string value "Any". Anything else gets selected and it
passes the company name to the query as normal... Im sure its not as simple
as 'If value = "any" then pass "" to query' so what am i missing? I am only
a intermediate/beginner to coding in access...

Thanks for any help.

Gary

 

[Ken Snell \(MVP\)]

Many ways to do this. For your situation, assuming that the value of "All"
in the combo box means that you want all non-Null values to be used in the
query:

SELECT FieldName
FROM TableName
WHERE CompanyName Like
IIf(Forms!FormName!ComboBoxName = "All",
"*", Forms!FormName!ComboBoxName);

 

[Guest]

lovely and precise reply, sir. thank you very much it worked like a charm.

 

[Ken Snell \(MVP\)]

You're welcome.