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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.

"Marshall Barton" <marshbarton@xxxxxxxxxx> wrote in message
news:osivs1ppqujuuq41t6cn0l8uevibpc760p@xxxxxxxxxx
> 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: Days Since Last Login IN ADUC Query
    ... Thanks again for your response Richard! ... Microsoft MVP Scripting and ADSI ... value is updated at logon only if the old value is more than 14 days old. ... I have created a query at the Root of the domain (and in varios OU's ...
    (microsoft.public.windows.server.active_directory)
  • Re: Programatic In() statement
    ... YEAH, your my hero Marshall!!! ... The list items must be separated by only a comma. ... of a query. ... Use a calulated field similar to: ...
    (microsoft.public.access.queries)
  • Re: Filter Query by Year on a form
    ... I want to be able to set up my report queries ... Those boxes are ... then the query will list every year in the ... Often applying criteria to an expression results in a query that is more ...
    (microsoft.public.access.queries)
  • Re: LDAP Query for Expired accounts
    ... Thanks Richard! ... > Also, in testing the script below, I found that my ldap query is ... > Distinguished Names of all expired user accounts follows. ... > Dim objShell, lngBiasKey, lngBias, k ...
    (microsoft.public.windows.server.active_directory)
  • Re: Printing parameter value on report
    ... Marshall, thanks for continuing the help and believe me, you did not waste my ... This is another case where using a query parameter prompt is ... With all that in place the report text box would be ... because when I add a text box and use the [Enter Destruction Date:] as the ...
    (microsoft.public.access.reports)