By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Passing multiple variables to a query

P: 2
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...
Jan 16 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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 ....

Expand|Select|Wrap|Line Numbers
  1. SELECT '*' AS CustID, '<All>' AS CustName
  2. UNION
  3. SELECT CustID, CustName
  4. FROM Customers;
Mary
Jan 16 '07 #2

P: 2
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 ....

Expand|Select|Wrap|Line Numbers
  1. SELECT '*' AS CustID, '<All>' AS CustName
  2. UNION
  3. SELECT CustID, CustName
  4. 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
Expand|Select|Wrap|Line Numbers
  1. 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? :-/
Jan 16 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I've got to be stupid, but I dont understand at all...

I thought that the query should be the issue..
Something like
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 16 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. WHERE ((...) AND ([Name] Like Forms!frmMyForm!cboName & '*') AND (...))
Does that make sense?
Jan 16 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.