Multiple combo boxes - send parameters to a query?

[Guest]

Hi guys, I've had this problem before and never fully resolved it so am
trying again, this time with a different tact.

I have a form containing 6 combo boxes. A sub form in the bottom of the form
changes depending on which combo boxes have parameters set. For the most part
this works well, however due to some of the combo boxes often being null, the
results shown aren't always 100% accurate - I am yet to resolve this problem.

I have been told before that this is the wrong way to run this kind of
query, and instead I should have the 6 combo boxes and a button which when
clicked runs the query based on the set parameters.

So, how do I do this? I remember being given some code but the instructions
were a little vague and I can't find that post any more. Detailed and
thorough instructions or even better a working example would be much
appreciated.

 

[Marshall Barton]

Hi guys, I've had this problem before and never fully resolved it so am
trying again, this time with a different tact.

I have a form containing 6 combo boxes. A sub form in the bottom of the form
changes depending on which combo boxes have parameters set. For the most part
this works well, however due to some of the combo boxes often being null, the
results shown aren't always 100% accurate - I am yet to resolve this problem.

I have been told before that this is the wrong way to run this kind of
query, and instead I should have the 6 combo boxes and a button which when
clicked runs the query based on the set parameters.

So, how do I do this? I remember being given some code but the instructions
were a little vague and I can't find that post any more. Detailed and
thorough instructions or even better a working example would be much
appreciated.

When you are having trounle with a query. it's a good idea
to poat a Copy/Paste of the query's SQL statement along with
an explation of how the combo boxes interact with it. After
all, if you are having trouble remembering a past reply, how
can you expect us to remember your situation.

 

[Guest]

Marshall - fair comment and I will paste the sql query below.

The reason I left it vague though is that I'm sure my solution is a common
problem, and I was just hoping for the generic code that passes combo box
parameters, if set, to a query.

As I said previously, the below query sort of works, but doesn't always
display all the results and this is apparently because of combos that might
be set as null.

SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Price, Order_Details.Denomination,
Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber
WHERE (((Orders.[Customer Name]) Like "*" & [cboQueriedCustomer]) AND
((Order_Details.[Item Type]) Like "*" & [cboProduct]) AND
((Order_Details.Description) Like "*" & [txtDescription] & "*") AND
((Order_Details.Type_Colour_Size) Like "*" & [txtTypeColourSize] & "*") AND
((Orders.Complete) Like "*" & [cboOrder_Complete]) AND ((Orders.[Customer's
Order Number]) Like "*" & [QueriedCustRef]) AND ((Orders.Contact) Like
Nz([cboQueriedContact],"*")))
ORDER BY Orders.OrderNumber DESC;

 

[Marshall Barton]

Well this is looking more than a little unusaul. Why are
you using Like "*" & ...? If you really need that kind of
comparison, it implies that there may be something funny in
you table's fields or that the combo boces only have a vague
relationship with each other???

In your particular approach below, you can avoid the issue
of some combo boxes being left blank (Null) by changing each
comaprison to include a null text. There is also a problem
where you are not using ful references to the combo boxes
and I don't see how you can get much of anything useful out
of the query you posted.

WHERE (((Orders.[Customer Name]) Like "*" &
Forms!yourform.cboQueriedCustomer OR
Forms!yourform.cboQueriedCustomer Is Null) AND . . .
--
Marsh
MVP [MS Access]

As I said previously, the below query sort of works, but doesn't always
display all the results and this is apparently because of combos that might
be set as null.

SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Price, Order_Details.Denomination,
Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber
WHERE (((Orders.[Customer Name]) Like "*" & [cboQueriedCustomer]) AND
((Order_Details.[Item Type]) Like "*" & [cboProduct]) AND
((Order_Details.Description) Like "*" & [txtDescription] & "*") AND
((Order_Details.Type_Colour_Size) Like "*" & [txtTypeColourSize] & "*") AND
((Orders.Complete) Like "*" & [cboOrder_Complete]) AND ((Orders.[Customer's
Order Number]) Like "*" & [QueriedCustRef]) AND ((Orders.Contact) Like
Nz([cboQueriedContact],"*")))
ORDER BY Orders.OrderNumber DESC;

 

[Guest]

Hi Marshall thanks for your help on this.

I will see if your suggestion works and will let you know.

One thing I forgot to mention is that not all of the parameter fields are
combo boxes which explain the need for Like "*" &

But if the code is wrong then it's the fault of whoever here on this forum
gave me it in the first place.

 

[Guest]

Hmm so that doesn't seem to get me anywhere. Let me elaborate further.

I have a main form called Orders_Queried this form contains 4 combo boxes
and 3 text fields. These 7 fields will be filled in by the user to filter out
the results in the sub form below called Order_Details query, but I want to
query any combination of the 7. And maybe not all 7 at the same time, but
maybe fields 1 and 2, or 3 4 and 5 etc.

The 7 fields are called:

cboQueriedCustomer
cboQueriedContact
cboOrder_Complete
cboProduct
txtQueriedCustRef
txtDescription
txtTypeColourSize

So how exactly do I set my query to check for any combination of the 7
fields being filled in? As well as comparing text strings like the 3 txt
fields?

 

[Marshall Barton]

What is "doesn't seem to get me anywhere" supposed to mean
to me?

What I posted should take care of the controls that have
nothing specified for use in the criteria. Did you apply my
example to all seven conditions? Do you have a requery in
each control's AfterUpdate event? I thought you said that
your original arrangement worked if you filled in all the
controls and you wanted to modify the conditions to allow
for a missing value. Does it still work the way it used to?
What happens if you leave just one of the control's blank?

I don't think it's fair for you to blame any problems on a
person that tried to help you previously. It's your
resonsibility to understand what their ideas do or don't do
and to adapt them to meet your specific needs. I will say
that the "*" before the value of a control is very unusual
and I have trouble understanding a situation where that is
useful, but you said it worked??

 

[Guest]

Hi Marshall

Thank you for your replies. I think you're taking me the wrong way however.
I am eternally grateful to everyone on this forum who has helped me. Without
help on from people on this forum, the project I am working on would be
nowhere near where it is now.

And I wasn't passing blame. You asked the question:

Why are you using Like "*" & ...? If you really need that kind of
comparison, it implies that there may be something funny in
you table's fields or that the combo boces only have a vague
relationship with each other???

And I replied by saying - I didn't write that script - another user had told
me to try that code previously. That is all.

And yes I admit, "doesn't seem to get me anywhere" is a bit of a vague reply
for which I apologise. To elaborate further, I did what I thought you were
telling me to do, and this generated some sort of infinitely looping query.
When I viewed the SQL of my query after I applied OR
Forms!yourform.cboQueriedCustomer Is Null to each criteria the SQL view just
filled with hundreds of OR and AND clauses before crashing ACCESS. So it was
back to square 1.

You seem awfully defensive in your replies but to re-iterate I appreciate
all the help I can get. A year ago I had never used Access but now have a
reasonable understanding of Access and databases in general, however my VB
skills are non existent so forgive my need for hand holding.

 

[Marshall Barton]

Thank you for your replies. I think you're taking me the wrong way however.
I am eternally grateful to everyone on this forum who has helped me. Without
help on from people on this forum, the project I am working on would be
nowhere near where it is now.

And I wasn't passing blame. You asked the question:

Why are you using Like "*" & ...? If you really need that kind of
comparison, it implies that there may be something funny in
you table's fields or that the combo boces only have a vague
relationship with each other???

And I replied by saying - I didn't write that script - another user had told
me to try that code previously. That is all.

And yes I admit, "doesn't seem to get me anywhere" is a bit of a vague reply
for which I apologise. To elaborate further, I did what I thought you were
telling me to do, and this generated some sort of infinitely looping query.
When I viewed the SQL of my query after I applied OR
Forms!yourform.cboQueriedCustomer Is Null to each criteria the SQL view just
filled with hundreds of OR and AND clauses before crashing ACCESS. So it was
back to square 1.

You seem awfully defensive in your replies but to re-iterate I appreciate
all the help I can get. A year ago I had never used Access but now have a
reasonable understanding of Access and databases in general, however my VB
skills are non existent so forgive my need for hand holding.

I probably did come across a little harsh. Must be my
frustration at trying to answer a question with so few clues
to go on.

Regardless, it sounds like the query designer blew up with
whatever you entered in the design grid. How about just
skipping that interface and making the changes directly in
SQL view? Does that work? If it doesn't blow up and you
have further questions, please post a Copy/Paste of the SQL
sstatement so I can see exactly what tried.

 

[Guest]

Hi Marshall - I think I might have solved this problem.

First though, I think Like "*" & [cboWhatever] & "*" is used to put *wild
card onto either end of a field, I've just been researching wild cards and
their normal usage is *searchterm* but when I tried *[cboWhatever]* no
results were found, however using "*" & [cbo] & "*" seems to work.

Anyway, I've just discovered the Nz function and I think might well solve my
problem, I have modified my query and it now reads:


SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Price, Order_Details.Denomination,
Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber
WHERE (((Orders.[Customer Name]) Like Nz([cboQueriedCustomer],"*"))
AND ((Order_Details.[Item Type]) Like Nz([cboProduct],"*"))
AND ((Order_Details.Description) Like "*" & [txtDescription] & "*")
AND ((Order_Details.Type_Colour_Size) Like "*" & [txtTypeColourSize] & "*")
AND ((Orders.Complete) Like Nz([cboOrder_Complete],"*"))
AND ((Orders.[Customer's Order Number]) Like "*" & [QueriedCustRef] & "*")
AND ((Orders.Contact) Like Nz([cboQueriedContact],"*")))
ORDER BY Orders.OrderNumber DESC;

This seems to work, but can you see any flaws to this methodology? I ask
because people keep telling me my query can't be done the way I want it but
the above seems to be working. I just need to verify my query's results by
manually comparing with the data in my table.

And thanks for your help Marshall, it's very much appreciated.

 

[Marshall Barton]

Hi Marshall - I think I might have solved this problem.

First though, I think Like "*" & [cboWhatever] & "*" is used to put *wild
card onto either end of a field, I've just been researching wild cards and
their normal usage is *searchterm* but when I tried *[cboWhatever]* no
results were found, however using "*" & [cbo] & "*" seems to work.

Anyway, I've just discovered the Nz function and I think might well solve my
problem, I have modified my query and it now reads:


SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Price, Order_Details.Denomination,
Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber
WHERE (((Orders.[Customer Name]) Like Nz([cboQueriedCustomer],"*"))
AND ((Order_Details.[Item Type]) Like Nz([cboProduct],"*"))
AND ((Order_Details.Description) Like "*" & [txtDescription] & "*")
AND ((Order_Details.Type_Colour_Size) Like "*" & [txtTypeColourSize] & "*")
AND ((Orders.Complete) Like Nz([cboOrder_Complete],"*"))
AND ((Orders.[Customer's Order Number]) Like "*" & [QueriedCustRef] & "*")
AND ((Orders.Contact) Like Nz([cboQueriedContact],"*")))
ORDER BY Orders.OrderNumber DESC;

This seems to work, but can you see any flaws to this methodology? I ask
because people keep telling me my query can't be done the way I want it but
the above seems to be working. I just need to verify my query's results by
manually comparing with the data in my table.

This is a tough question. Logically, I think that your
approach makes sense. However, the Like operator uses
string operands so numeric and date fields will either fail
or first go through a conversion process before Like can do
its thing. It has been a very long time since I last used
Like Nz(xxx,"*") to match any value, but it did work at that
time. Note that it is inefficient and may be too slow in
some situations, but only you can decide if it's acceptable.
I think you should go with this until you run into a problem
with it, then maybe you will see more sophisticated
alternatives.

One thing you have that I find extraordinay is that you are
referencing form controls without specifying the full
reference. I would have expected the query to prompt you to
enter values for [cboQueriedCustomer], [cboProduct], etc.
If those really are controls on a form where users enter the
criteria values, then the book says the references should be
Forms!nameofform.cboQueriedCustomer,
Forms!nameofform.cboProduct, etc.

Your reason for using "*" & xxx & "*" makes sense, but it is
not what I was questioning. The syntax I was questioning
was where your earlier query had a couple of conditions that
were "*" & xxx without "*" on the right, which, when xxx
is not null, will only match records that end in the xxx
string. Now, with your use of Nz, you are not trying to do
those odd partial matches so it is no longer a question.

 

[John Spencer]

Marshall and Richard,
I might add one other thing. IF the fields you are applying criteria
against are NULL the use of LIKE NZ([Something],"*") will cause those
records with a null value to be filtered out of the results. For instance
if Order_Details.Type_Colour_Size ever contains nothing and you leave
txtTypeColourSize blank(null) then any records that don't have a color size
will not be returned by the query. The exception to this problem is if the
field is not null but is a zero-length string.

Hi Marshall - I think I might have solved this problem.

First though, I think Like "*" & [cboWhatever] & "*" is used to put *wild
card onto either end of a field, I've just been researching wild cards and
their normal usage is *searchterm* but when I tried *[cboWhatever]* no
results were found, however using "*" & [cbo] & "*" seems to work.

Anyway, I've just discovered the Nz function and I think might well solve
my
problem, I have modified my query and it now reads:


SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Price, Order_Details.Denomination,
Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber
WHERE (((Orders.[Customer Name]) Like Nz([cboQueriedCustomer],"*"))
AND ((Order_Details.[Item Type]) Like Nz([cboProduct],"*"))
AND ((Order_Details.Description) Like "*" & [txtDescription] & "*")
AND ((Order_Details.Type_Colour_Size) Like "*" & [txtTypeColourSize] &
"*")
AND ((Orders.Complete) Like Nz([cboOrder_Complete],"*"))
AND ((Orders.[Customer's Order Number]) Like "*" & [QueriedCustRef] & "*")
AND ((Orders.Contact) Like Nz([cboQueriedContact],"*")))
ORDER BY Orders.OrderNumber DESC;

This seems to work, but can you see any flaws to this methodology? I ask
because people keep telling me my query can't be done the way I want it
but
the above seems to be working. I just need to verify my query's results by
manually comparing with the data in my table.

This is a tough question. Logically, I think that your
approach makes sense. However, the Like operator uses
string operands so numeric and date fields will either fail
or first go through a conversion process before Like can do
its thing. It has been a very long time since I last used
Like Nz(xxx,"*") to match any value, but it did work at that
time. Note that it is inefficient and may be too slow in
some situations, but only you can decide if it's acceptable.
I think you should go with this until you run into a problem
with it, then maybe you will see more sophisticated
alternatives.

One thing you have that I find extraordinay is that you are
referencing form controls without specifying the full
reference. I would have expected the query to prompt you to
enter values for [cboQueriedCustomer], [cboProduct], etc.
If those really are controls on a form where users enter the
criteria values, then the book says the references should be
Forms!nameofform.cboQueriedCustomer,
Forms!nameofform.cboProduct, etc.

Your reason for using "*" & xxx & "*" makes sense, but it is
not what I was questioning. The syntax I was questioning
was where your earlier query had a couple of conditions that
were "*" & xxx without "*" on the right, which, when xxx
is not null, will only match records that end in the xxx
string. Now, with your use of Nz, you are not trying to do
those odd partial matches so it is no longer a question.

 

[Guest]

John - Marshall - thank for your comments.

In answer to John's question - you were absolutely right about records with
blank fields being missing from my query's results. I did a manually query to
validate the results of my combo query and there were 2 results less than in
my manual query - but as you rightly said, the two missing records had one or
more fields left empty. I will make a point of telling my colleagues to
ensure they fill in every single field on the form.

Marshall, so far the query runs very quickly and I'm not noticing any
performance issues. Obviously as the data size increases this may change, but
in the meantime it seems work exactly as I hoped it would.

Luckily - none of the fields I'm searching are numeric or date fields,
they're all text strings, so in this scenario I think this approach is
perfect. Also the txt fields are more of a last resort for if you can't find
the data you want from the cbo parameters. I would hope that at most, only
single words will be entered in the txtDescription and txtTypeColourSize
fields - in which case I think I have acheived my goal for now.

As for having to refer to the cbo fully, I can only summise that this works
because the combo and txt fields are on a single form with the query running
in a subform and rightly or wrongly, the query picks up the data in the cbo
and text boxes because they are in such close proximity.

Thank you ever so much.

 

[Marshall Barton]

Richard Horne wrote:
[]

As for having to refer to the cbo fully, I can only summise that this works
because the combo and txt fields are on a single form with the query running
in a subform and rightly or wrongly, the query picks up the data in the cbo
and text boxes because they are in such close proximity.

This is a new one on me. All I can say is that I have no
idea how they do that ;-) Anyway, keep it in mind in case
it is context or version dependent.

Regardless, it's good to hear that you're up and running.