Form Criteria

[ajetrumpet]

Say I have 10 dropdowns in a form and a user can select any combination or all of them for the underlying query criteria. Do I need to manually write this query in design or SQL using [AND's] and [OR's], or can it be simplified?

-J

 

[PHV]

Which kind of simplification do you expect ?
A common way is to build the WHERE clause (ie the criteria) in VBA, depending the user's choices.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ajetrumpet]

Which kind of simplification do you expect

Well, hello again PHV....this code is from a DB I made a while back for an invoice log....

SELECT Invoices.ProjectNo, Invoices.Vendor, Invoices.PO, Invoices.InvoiceNumber, Invoices.Voucher, Invoices.Amount
FROM Invoices
WHERE ((Invoices.ProjectNo)=Forms![Search Form]![Project Number] Or Forms![Search Form]![Project Number] Is Null) And ((Invoices.Vendor)=Forms![Search Form]!Vendor Or Forms![Search Form]!Vendor Is Null) And ((Invoices.PO)=Forms![Search Form]![Purchase Order] Or Forms![Search Form]![Purchase Order] Is Null) And ((Invoices.InvoiceNumber)=Forms![Search Form]![Invoice Number] Or Forms![Search Form]![Invoice Number] Is Null) And ((Invoices.Voucher)=Forms![Search Form]![Voucher Number] Or Forms![Search Form]![Voucher Number] Is Null) And ((Invoices.Amount)=Forms![Search Form]!Amount Or Forms![Search Form]!Amount Is Null);

Look at all those subqueries!  How about all those AND's and OR's??  Can you simplify this?

 

[ajetrumpet]

Maybe you can read it better this way...

SELECT Invoices.ProjectNo, Invoices.Vendor, Invoices.PO, Invoices.InvoiceNumber, Invoices.Voucher, Invoices.Amount
FROM Invoices
WHERE ((Invoices.ProjectNo)=Forms![Search Form]![Project Number] Or Forms![Search Form]![Project Number] Is Null) And ((Invoices.Vendor)=Forms![Search Form]!Vendor Or Forms![Search Form]!Vendor Is Null) And ((Invoices.PO)=Forms![Search Form]![Purchase Order] Or Forms![Search Form]![Purchase Order] Is Null) And ((Invoices.InvoiceNumber)=Forms![Search Form]![Invoice Number] Or Forms![Search Form]![Invoice Number] Is Null) And ((Invoices.Voucher)=Forms![Search Form]![Voucher Number] Or Forms![Search Form]![Voucher Number] Is Null) And ((Invoices.Amount)=Forms![Search Form]!Amount Or Forms![Search Form]!Amount Is Null);

 

[PHV]

Look at all those subqueries
There is NO subquery at all in this SQL code ...

For each field not allowing Null value you may simplify this:
AND (([Name of field])=Forms![Search Form]![Name of control] Or Forms![Search Form]![Name of control] Is Null)
Like this:
AND [Name of field]=Nz(Forms![Search Form]![Name of control],[Name of field])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ajetrumpet]

Thus, the purpose of the Nz is....?
Definition?

 

[PHV]

Put the cursor inside the Nz word in the code and press the F1 key.

 

[ajetrumpet]

PH,

I know where the help menu is....you should know a little bit about me by now. I guess you don't say very much, how long have you been working with MIS?

 

[PHV]

I don't suggested the help menu but the context sensitive help system.
As for your second question, simply have a look at my profile:

http://www.tek-tips.com/userinfo.cfm?member=PHV

 

[ajetrumpet]

I know where the profiles are as well. The purpose was to invite a bit of conversation, but if you're not into that, I apologize. You certainly don't seem to be....

From my understanding, Nz is meant to substitute a value for a control is the variant is Null. If THIS form control is Null, I want it to stay Null, but also be included as query criteria. Making sense?

 

[PHV]

Did you try it ?

 

[ajetrumpet]

Yes, I tried the Nz function and come up with blank results

 

[PHV]

Any chance you could post the non working SQL code (with Nz) AND the same but working SQL code (with Or ... Is Null) ?

 

[ajetrumpet]

Here is the one working (for any combo of criteria entered)

SELECT Invoices.ProjectNo, Invoices.Vendor, Invoices.PO, Invoices.InvoiceNumber, Invoices.Voucher, Invoices.Amount
FROM Invoices
WHERE (Invoices.ProjectNo=Forms![Search Form]![Project Number] Or Forms![Search Form]![Project Number] Is Null)
And (Invoices.Vendor=Forms![Search Form]!Vendor Or Forms![Search Form]!Vendor Is Null) 
And (Invoices.PO=Forms![Search Form]![Puchase Order] Or Forms![Search Form]![Puchase Order] Is Null)
And (Invoices.InvoiceNumber=Forms![Search Form]![Invoice Number] Or Forms![Search Form]![Invoice Number] Is Null)
And (Invoices.Voucher=Forms![Search Form]![Voucher Number] Or Forms![Search Form]![Voucher Number] Is Null)
And (Invoices.Amount=Forms![Search Form]!Amount Or Forms![Search Form]!Amount Is Null);

Here is the code for Nz, which doesn't produce the same result (actually is produces nothing when run...)

SELECT Invoices.ProjectNo, Invoices.Vendor, Invoices.PO, Invoices.InvoiceNumber, Invoices.Voucher, Invoices.Amount
FROM Invoices
WHERE Invoices.ProjectNo=Nz(Forms![Search Form]![Project Number],[Invoices.ProjectNo])
And Invoices.Vendor=Nz(Forms![Search Form]![Vendor],[Invoices.Vendor])
And Invoices.PO=Nz(Forms![Search Form]![Puchase Order],[Invoices.PO])
And Invoices.InvoiceNumber=Nz(Forms![Search Form]![Invoice Number],[Invoices.InvoiceNumber])
And Invoices.Voucher=Nz(Forms![Search Form]![Voucher Number],[Invoices.Voucher])
And Invoices.Amount=Nz(Forms![Search Form]![Amount],[Invoices.Amount]);

something missing?  I don't see anything that could be...

 

[PHV]

Could any of the 6 Invoices's fields be Null ?
You may try this:

SELECT ProjectNo, Vendor, PO, InvoiceNumber, Voucher, Amount
FROM Invoices
WHERE Nz(ProjectNo)=Nz(Forms![Search Form]![Project Number],Nz(ProjectNo))
AND Nz(Vendor)=Nz(Forms![Search Form]![Vendor],Nz(Vendor))
AND Nz(PO)=Nz(Forms![Search Form]![Puchase Order],Nz(PO))
AND Nz(InvoiceNumber)=Nz(Forms![Search Form]![Invoice Number],Nz(InvoiceNumber))
AND Nz(Voucher)=Nz(Forms![Search Form]![Voucher Number],Nz(Voucher))
AND Nz(Amount)=Nz(Forms![Search Form]![Amount],Nz(Amount));

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[maxhugen]

G'day ajetrumpet

In my apps I use a 'Report Manager' form which has a list of all available reports. These are actually grouped, as there are a lot, and the user selects which group to peruse using an option group - this requeries the list.

A lot of the reports have multiple criteria. The criteria that's applicable to each report varies, so I use a reports table which uses Y/N fields to specify which criteria is available for which report, and hides/shows the available criteria as the user clicks on a report in the list. Using the table makes it easy to use a loop to set criteria visibility.

Once the user clicks on Preview or Print, one function prepares the WHERE clause.

It loops through each of the criteria (often combo boxes, sometimes date ranges, sometimes 'free' text entry.

IF the criteria is Visible (ie its avail for this report), AND its not Null (so the user has actually specified some criteria in this control), then it is added to the WHERE clause.

In your case (where all criteria is visible anyway), I'd use something like this in my print function:

Dim sCrit$

    If Not IsNull(Forms![Search Form]![Project Number]) then
        sCrit = sCrit & iif(sCrit<>""," AND ","")
        sCrit = sCrit & "Invoices.ProjectNo='" & _
                Forms![Search Form]![Project Number] & "'"
    End If
    If Not IsNull(Forms![Search Form]!Vendor) then
        sCrit = sCrit & iif(sCrit<>""," AND ","")
        sCrit = sCrit & "Invoices.Vendor='" & _
                Forms![Search Form]!Vendor & "'"
    End If
    If Not IsNull(Forms![Search Form]![Puchase Order]) then
        sCrit = sCrit & iif(sCrit<>""," AND ","")
        sCrit = sCrit & "Invoices.PO='" & _
                Forms![Search Form]![Puchase Order] & "'"
    End If
    If Not IsNull(Forms![Search Form]![Invoice Number]) then
        sCrit = sCrit & iif(sCrit<>""," AND ","")
        sCrit = sCrit & "Invoices.InvoiceNumber='" & _
                Forms![Search Form]![Invoice Number] & "'"
    End If
    If Not IsNull(Forms![Search Form]![Voucher Number]) then
        sCrit = sCrit & iif(sCrit<>""," AND ","")
        sCrit = sCrit & "Invoices.Voucher='" & _
                Forms![Search Form]![Voucher Number] & "'"
    End If
    If Not IsNull(Forms![Search Form]!Amount) then
        sCrit = sCrit & iif(sCrit<>""," AND ","")
        sCrit = sCrit & "Invoices.Amount=" & _
                Forms![Search Form]!Amount
    End If

    DoCmd.OpenReport "myReport", acViewPreview, sCrit

Please note that I have assumed that every field except Amount is a text field in your table - of course that may not be the case, so the above would need to be changed accordingly.

HTH

Max Hugen
Australia

 

[ajetrumpet]

Could any of the 6 Invoices' fields be Null?

Yes, this is entirely at the user's discretion, and I have gotten the Nz code to work, and I understand it as well. If all combos are left blank though, the entire table is shown in the search (via the [ValueifNull] portion). I understand why but I don't want it (not that it matters anyway). This DB is part of a sample group that I have for instruction. Thanks PHV, I'll let you go on in silence again. =)

Hey Max,
Your code is a bit complicated, but I don't think I should post it in a sample like the one I have. Thank you for post though, I will probably reference this in the future sometime when I can hone my programming skills a little more.
It wouldn't do me that good anyway, MS Access SQL view is absolutely horrid! What's with the program default view of stringing all the code together? I think someone on this site actually asked about that one time....

 

[maxhugen]

MS Access SQL view is absolutely horrid

I guess that the Access dev team at MS never bothered too much with the SQL view of a query, because most people use the Query grid which IMHO is one of the best graphical query builders around. Considering how long ago that was 'invented', I take my hat off to whoever dreamed it up!

It wouldn't do me that good anyway

Hope you didn't misunderstand me... that example is VBA code that you can insert into the code that runs when you click your 'Preview' button. So your query won't need any criteria at all, as it's supplied at runtime by DoCmd.OpenReport

Max Hugen
Australia