Re: Multiple combo boxes - send parameters to a query?

Richard Horne wrote:

>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.

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: Clearing all fields with code problem
    ... In a saved query you could use the following as criteria ... The cards are assigned to staff in our building, so the card is either blank, I.e no one has that card or someone has the card and the record is filled in. ... There is only one table apart from several small tables that populate the drop down boxes. ... What code should I enter that will check if there is any thing to delete in my OLE picture field ...
    (microsoft.public.access.gettingstarted)
  • Re: Problem with VBA Code behind form
    ... >> through which you can see the report. ... graphics card. ... the query calls from the table details of all ... >> selection is limited further by only choosing the records checked on ...
    (microsoft.public.access.forms)
  • Re: Problem with VBA Code behind form
    ... > with Radeon 64Mb graphics card. ... >> show up on the report, ... I'd execute an update query ...
    (microsoft.public.access.forms)
  • Re: Duplicates returned in report -- DISTINCT, DISTINCTROW
    ... SELECT DISTINCT [Recipe Ingredients].Quantity, Recipes.Instructions, ... UniqueValues and UniqueRecords are mutually exclusive -- you ... They are part of the Query ... instructions) in the form of a 5x8 index card. ...
    (microsoft.public.access.reports)
  • Re: Broken Queries: cant ref controls or functions
    ... > referencing controls on forms for param values, ... > With the sql above, with the two forms open to records, both refs to the ... However, during a major code routine, I ... > call a query that refs these form controls, ...
    (microsoft.public.access.formscoding)