Multiple Combo Boxes on Form

[MaryMalone]

I have a query that has 6 combo boxes linked on a form. I want to be able to
set the criteria so that the user can pick any combination of the combo
boxes, including leaving some or all blank, and return only the values that
match. What is the easiest way to write the query without having to type out
every combination that could happen? Thoughts?

Thanks!
Mary

 

[KARL DEWEY]

[Forms]![YourForm]![Combo1] OR [Forms]![YourForm]![Combo1] Is Null

 

[Wayne-I-M]

Hi Mary

Use QBF which will work fine. But, 6 combos ?. It depends on how much data
is in the query - it may take a while to run with 6 QBF's.

Lets say you have a form (frmMarysForm) with 6 combos
cbo1
cbo2
cb3
etc

Open your query in design view
In the critiera row which has the same data as cbo1
Forms![frmMarysForm]![cbo1] Or Forms![frmMarysForm]![cbo1] Is Null

In the critiera row which has the same data as cbo2
Forms![frmMarysForm]![cbo2] Or Forms![frmMarysForm]![cbo2] Is Null

In the critiera row which has the same data as cbo3
Forms![frmMarysForm]![cbo3] Or Forms![frmMarysForm]![cbo3] Is Null

Note that access will alter this QBF when you close the query and split the
critiera into 2 columns (12 in all in your case) - so if you look at the
design again it will look a little different

Next you need to decide what you're going to do with this filter data. You
could requery your form or (beter idea, open another form with just the
filtered data), run a report, etc.etc But thats another post : - )

good luck

 

[MaryMalone]

Thanks for replying. I have done what you said below, but my problem is that
the user will pick Cb1, Cb3, and Cb5 and leave the rest blank and only want
to see the records that relate to that combination. But with 6 combos (yes, I
know that seems ridiculous) there could be quite a few combinations. Is there
a good way to add all of those combinations to the query?

Hi Mary

Use QBF which will work fine. But, 6 combos ?. It depends on how much data
is in the query - it may take a while to run with 6 QBF's.

Lets say you have a form (frmMarysForm) with 6 combos
cbo1
cbo2
cb3
etc

Open your query in design view
In the critiera row which has the same data as cbo1
Forms![frmMarysForm]![cbo1] Or Forms![frmMarysForm]![cbo1] Is Null

In the critiera row which has the same data as cbo2
Forms![frmMarysForm]![cbo2] Or Forms![frmMarysForm]![cbo2] Is Null

In the critiera row which has the same data as cbo3
Forms![frmMarysForm]![cbo3] Or Forms![frmMarysForm]![cbo3] Is Null

Note that access will alter this QBF when you close the query and split the
critiera into 2 columns (12 in all in your case) - so if you look at the
design again it will look a little different

Next you need to decide what you're going to do with this filter data. You
could requery your form or (beter idea, open another form with just the
filtered data), run a report, etc.etc But thats another post : - )

good luck

--
Wayne
Manchester, England.

I have a query that has 6 combo boxes linked on a form. I want to be able to
set the criteria so that the user can pick any combination of the combo
boxes, including leaving some or all blank, and return only the values that
match. What is the easiest way to write the query without having to type out
every combination that could happen? Thoughts?

Thanks!
Mary

 

[Wayne-I-M]

Yes - QBF will work fine

Use the formula I gave you

Forms![frmMarysForm]![ComboName] Or Forms![frmMarysForm]![ComboName] Is Null

The Is Null section will allow for some of the combos to have nothing selected

Oh - my freind sai to say that QBF = query by form (but I assumed you
already know that)


--
Wayne
Manchester, England.

Thanks for replying. I have done what you said below, but my problem is that
the user will pick Cb1, Cb3, and Cb5 and leave the rest blank and only want
to see the records that relate to that combination. But with 6 combos (yes, I
know that seems ridiculous) there could be quite a few combinations. Is there
a good way to add all of those combinations to the query?

Hi Mary

Use QBF which will work fine. But, 6 combos ?. It depends on how much data
is in the query - it may take a while to run with 6 QBF's.

Lets say you have a form (frmMarysForm) with 6 combos
cbo1
cbo2
cb3
etc

Open your query in design view
In the critiera row which has the same data as cbo1
Forms![frmMarysForm]![cbo1] Or Forms![frmMarysForm]![cbo1] Is Null

In the critiera row which has the same data as cbo2
Forms![frmMarysForm]![cbo2] Or Forms![frmMarysForm]![cbo2] Is Null

In the critiera row which has the same data as cbo3
Forms![frmMarysForm]![cbo3] Or Forms![frmMarysForm]![cbo3] Is Null

Note that access will alter this QBF when you close the query and split the
critiera into 2 columns (12 in all in your case) - so if you look at the
design again it will look a little different

Next you need to decide what you're going to do with this filter data. You
could requery your form or (beter idea, open another form with just the
filtered data), run a report, etc.etc But thats another post : - )

good luck

--
Wayne
Manchester, England.

I have a query that has 6 combo boxes linked on a form. I want to be able to
set the criteria so that the user can pick any combination of the combo
boxes, including leaving some or all blank, and return only the values that
match. What is the easiest way to write the query without having to type out
every combination that could happen? Thoughts?

Thanks!
Mary

 

[MaryMalone]

I tried this and it is not working correctly on my form. It did like you
said, and added more columns and a bunch more lines with that formula. The
problem is that it does not allow me to pick combo 5 and leave the rest
blank. Does the automatic columns and lines that are added build in some kind
of priority or something, that I would have to pick one combo before another
or something? Any other ideas?

Yes - QBF will work fine

Use the formula I gave you

Forms![frmMarysForm]![ComboName] Or Forms![frmMarysForm]![ComboName] Is Null

The Is Null section will allow for some of the combos to have nothing selected

Oh - my freind sai to say that QBF = query by form (but I assumed you
already know that)


--
Wayne
Manchester, England.

Thanks for replying. I have done what you said below, but my problem is that
the user will pick Cb1, Cb3, and Cb5 and leave the rest blank and only want
to see the records that relate to that combination. But with 6 combos (yes, I
know that seems ridiculous) there could be quite a few combinations. Is there
a good way to add all of those combinations to the query?

Hi Mary

Use QBF which will work fine. But, 6 combos ?. It depends on how much data
is in the query - it may take a while to run with 6 QBF's.

Lets say you have a form (frmMarysForm) with 6 combos
cbo1
cbo2
cb3
etc

Open your query in design view
In the critiera row which has the same data as cbo1
Forms![frmMarysForm]![cbo1] Or Forms![frmMarysForm]![cbo1] Is Null

In the critiera row which has the same data as cbo2
Forms![frmMarysForm]![cbo2] Or Forms![frmMarysForm]![cbo2] Is Null

In the critiera row which has the same data as cbo3
Forms![frmMarysForm]![cbo3] Or Forms![frmMarysForm]![cbo3] Is Null

Note that access will alter this QBF when you close the query and split the
critiera into 2 columns (12 in all in your case) - so if you look at the
design again it will look a little different

Next you need to decide what you're going to do with this filter data. You
could requery your form or (beter idea, open another form with just the
filtered data), run a report, etc.etc But thats another post : - )

good luck

--
Wayne
Manchester, England.



:

I have a query that has 6 combo boxes linked on a form. I want to be able to
set the criteria so that the user can pick any combination of the combo
boxes, including leaving some or all blank, and return only the values that
match. What is the easiest way to write the query without having to type out
every combination that could happen? Thoughts?

Thanks!
Mary

 

[Wayne-I-M]

I search around and found this

http://support.microsoft.com/kb/304428/en-us

Have a look at it and try to follow it exactly - then, after you understand
the process bring the method into your DB

Good luck

--
Wayne
Manchester, England.

I tried this and it is not working correctly on my form. It did like you
said, and added more columns and a bunch more lines with that formula. The
problem is that it does not allow me to pick combo 5 and leave the rest
blank. Does the automatic columns and lines that are added build in some kind
of priority or something, that I would have to pick one combo before another
or something? Any other ideas?

Yes - QBF will work fine

Use the formula I gave you

Forms![frmMarysForm]![ComboName] Or Forms![frmMarysForm]![ComboName] Is Null

The Is Null section will allow for some of the combos to have nothing selected

Oh - my freind sai to say that QBF = query by form (but I assumed you
already know that)


--
Wayne
Manchester, England.

Thanks for replying. I have done what you said below, but my problem is that
the user will pick Cb1, Cb3, and Cb5 and leave the rest blank and only want
to see the records that relate to that combination. But with 6 combos (yes, I
know that seems ridiculous) there could be quite a few combinations. Is there
a good way to add all of those combinations to the query?

:

Hi Mary

Use QBF which will work fine. But, 6 combos ?. It depends on how much data
is in the query - it may take a while to run with 6 QBF's.

Lets say you have a form (frmMarysForm) with 6 combos
cbo1
cbo2
cb3
etc

Open your query in design view
In the critiera row which has the same data as cbo1
Forms![frmMarysForm]![cbo1] Or Forms![frmMarysForm]![cbo1] Is Null

In the critiera row which has the same data as cbo2
Forms![frmMarysForm]![cbo2] Or Forms![frmMarysForm]![cbo2] Is Null

In the critiera row which has the same data as cbo3
Forms![frmMarysForm]![cbo3] Or Forms![frmMarysForm]![cbo3] Is Null

Note that access will alter this QBF when you close the query and split the
critiera into 2 columns (12 in all in your case) - so if you look at the
design again it will look a little different

Next you need to decide what you're going to do with this filter data. You
could requery your form or (beter idea, open another form with just the
filtered data), run a report, etc.etc But thats another post : - )

good luck

--
Wayne
Manchester, England.



:

I have a query that has 6 combo boxes linked on a form. I want to be able to
set the criteria so that the user can pick any combination of the combo
boxes, including leaving some or all blank, and return only the values that
match. What is the easiest way to write the query without having to type out
every combination that could happen? Thoughts?

Thanks!
Mary