Passing multiple variables to a query

[Hapnadsmannen]

Greetings.

I have a problem that I have too little experience to solve, so I hope you guys can point me in the right direction:

I have 5 comboboxes with Name, Activity, Product, Project and Customer.
I want to create a report based on a query in which I freely can use 1 or more of the above criterias, i.e:
Just Name, or Name AND Project, or Project AND Product AND Name, or Customer AND Name, or just Customer, or.... well I think you get the scenario.
IS THIS AT ALL POSSIBLE IN JUST ONE QUERY?
or how will I go about to get this report to become real?

Hope you can help me...

[MMcCarthy]

Greetings.

I have a problem that I have too little experience to solve, so I hope you guys can point me in the right direction:

I have 5 comboboxes with Name, Activity, Product, Project and Customer.
I want to create a report based on a query in which I freely can use 1 or more of the above criterias, i.e:
Just Name, or Name AND Project, or Project AND Product AND Name, or Customer AND Name, or just Customer, or.... well I think you get the scenario.
IS THIS AT ALL POSSIBLE IN JUST ONE QUERY?
or how will I go about to get this report to become real?

Hope you can help me...

Each of the combo boxes could have a default '<All>' value which means if you don't select a value it will return all values.

The way to get <All> is something like the following using Customers Table as an example.

Set the combo box row source to ....

SELECT '*' AS CustID, '<All>' AS CustName
UNION
SELECT CustID, CustName
FROM Customers;

Mary

[Hapnadsmannen]

Each of the combo boxes could have a default '<All>' value which means if you don't select a value it will return all values.

The way to get <All> is something like the following using Customers Table as an example.

Set the combo box row source to ....

SELECT '*' AS CustID, '<All>' AS CustName
UNION
SELECT CustID, CustName
FROM Customers;

Mary

I've got to be stupid, but I dont understand at all...

I thought that the query should be the issue..
Something like

SELECT * FROM Projectdata WHERE Projectdata.Name  = cboName.Value AND Projectdata.Activity = cboActivity.Value AND Projectdata.Customer = cboCustomer.Value 

and so on...
But that SQL-code assumes that every cbo-box.Value really has a value in it.
If I try this and only choose values from one or more, but not all cbo-boxes then the query results in nothing.

Or am I totally lost? :-/

[MMcCarthy]

I've got to be stupid, but I dont understand at all...

I thought that the query should be the issue..
Something like

SELECT * FROM Projectdata WHERE Projectdata.Name  = cboName.Value AND Projectdata.Activity = cboActivity.Value AND Projectdata.Customer = cboCustomer.Value 

and so on...
But that SQL-code assumes that every cbo-box.Value really has a value in it.
If I try this and only choose values from one or more, but not all cbo-boxes then the query results in nothing.

Or am I totally lost? :-/

If you use the default value of <All> this will result in the '*' wildcard which will return all values if no other value is selected.

Mary

[NeoPa]

An alternative would be to structure the WHERE clause of your query for each field so that if the criteria is not specified at all, it will return all items.
An example for a Name field using cboName on the form frmMyForm would be :

WHERE ((...) AND ([Name] Like Forms!frmMyForm!cboName & '*') AND (...))

Does that make sense?