Query that will search for mulitiple criteria in mulitiple combinations

[Leah Trahan]

I've got a query that returns 8 fields, 7 of which I need to search for in various combinations and the other 1 (Cost) just to view its contents. Below is the mess I've made of it so far:

SELECT tblNCRRpt.dtmNCRDate, tblNCRRpt.idsNCRNum, tblNCRRpt.chrVendr, tblNCRRpt.chrPartNam, tblNCRRpt.chrPartNum, tblNCRRpt.chrSerNum, tblNCRRpt.chrDisp, tblNCRRpt.curCost
FROM tblNCRRpt
WHERE (((tblNCRRpt.idsNCRNum) Like [Forms]![frmSrchNCR]![txtNCRNum])) OR (((tblNCRRpt.chrVendr) Like [Forms]![frmSrchNCR]![txtVendr])) OR (((tblNCRRpt.chrPartNum) Like [Forms]![frmSrchNCR]![txtPartNum]) AND ((tblNCRRpt.chrSerNum) Like [Forms]![frmSrchNCR]![txtSerNum])) OR (((tblNCRRpt.chrDisp) Like [Forms]![frmSrchNCR]![cboDisp])) OR (((tblNCRRpt.dtmNCRDate) Between [Forms]![frmSrchNCR]![txtDate1] And [Forms]![frmSrchNCR]![txtDate2])) OR (((tblNCRRpt.dtmNCRDate) Between [Forms]![frmSrchNCR]![txtDate1] And [Forms]![frmSrchNCR]![txtDate2]) AND ((tblNCRRpt.chrPartNum) Like [Forms]![frmSrchNCR]![txtPartNum])) OR (((tblNCRRpt.dtmNCRDate) Between [Forms]![frmSrchNCR]![txtDate1] And [Forms]![frmSrchNCR]![txtDate2]) AND ((tblNCRRpt.chrDisp) Like [Forms]![frmSrchNCR]![cboDisp])) OR (((tblNCRRpt.chrPartNum) Like [Forms]![frmSrchNCR]![txtPartNum]))
ORDER BY tblNCRRpt.dtmNCRDate, tblNCRRpt.idsNCRNum;

Obviously, this only works for some of the comibnations, which doesn't help me. I need ALL search combinations to work. Any ideas?

[cyberdwarf]

WOW! You mean that's not all the combinations???

If not all combinations are used in particular cases, you might simplify things by constructing your query on the fly, depending on which form fields have valid values at the time...

dim strSQL as String
strSQL = "Select * From tblName Where "
If Not IsNull(Form1!FldA) Then
    strSQL = strSQL & "tblName.FldX = " & Form1!FldA
End If

and so on

HTH

Steve

[Leah Trahan]

Steve, I am not good enough at this yet to be able to apply your answer. Maybe if I could give a little more info (Note: initial question should be corrected to 6 field to search for and 2 just for viewing)

The user will enter values in the following controls (located all on the same form) in different combinations (depending on what they are searching for) (and some of them being left null): (1) a date range (txtDate1) & (txtDate2), (2) an NCR# (idsNCRNum) (which is also the primary key of the main table all these search criteria are located in), (3) a serial# (txtSerNum), (4) a part# (txtPartNum), (5) a disposition type (txtDisp), (6) a vendor (txtVendr).

Example searches: (1) for all records during a given date range, (2) for a serial# (3) for a part# (4) for a serial# AND part# (where that part# and ser# occur together in the same record), (5) for a NCR#, (6) for disposition (7) for a Vendor, etc.....NOTING that all of these may or may not be searched for with a date range included and also in different combinations with each other.

Maybe your reply answered all of this, but I'm not advanced enough to know, sorry. I appreciate all help on this!

[nico5038]

Te easy way will be to have a mainform with a datasheet subform based on your query.
Now instruct the user to use the right-click popup menu. This will issue a filter to the rows and by adding new criteria the filter will "narrow" it down by using an AND relation. Even a Like can be specified this way.

The other option would be to have fields (or combo's) for your 7 fields and let the user enter the needed strings. The with code you need to test for filled fields and create a filter for the subform yourself. Guess you know now why I prefer the right-click :-)

Nic;o)

[Leah Trahan]

Te easy way will be to have a mainform with a datasheet subform based on your query.
Now instruct the user to use the right-click popup menu. This will issue a filter to the rows and by adding new criteria the filter will "narrow" it down by using an AND relation. Even a Like can be specified this way.

The other option would be to have fields (or combo's) for your 7 fields and let the user enter the needed strings. The with code you need to test for filled fields and create a filter for the subform yourself. Guess you know now why I prefer the right-click :-)

Nic;o)

Based on the users I will have, I will need something that is as user-friendly as possible, thus, having them fill in the fields will be my unfortunate answer. I already have a form set up where they will enter the fields they so choose. Right below these entry boxes is a tabular query subform that I want to show them the results of their "search".

Your idea to test for filled fields and then filter the subform myself makes perfect sense and is what I've been I've been stuck on for the last 3 days! I don't know how to write code well enough yet. Could you give me some example of how to start and then maybe I can figure enough of it out to fill in the rest of the blanks...? Thanks!

[nico5038]

OK, then place a [Filter] button behind the fields.
Next open the properties for the button (button with hand and white sheet) and under the event tab you'll find the OnClick event. Doubleclick on that so [Event Procedure] will appear.
Now press the [...] button at the end and the code form will open with the cursor in the procedure needed.
Now type:

dim strFilter as string
 
' empty string
strFilter = ""
' test text field filled
IF len(nz(me.txtFieldText)) > 0 then
    strFilter = strFilter & " and FieldText='" & me.txtFieldText & "'"
endif
' test numeric field filled
IF len(nz(me.txtFieldNum)) > 0 then
    strFilter = strFilter & " and FieldNum=" & me.txtFieldNum 
endif
' test date field filled
IF len(nz(me.txtFieldDate)) > 0 then
    strFilter = strFilter & " and FieldDate=#" & me.txtFieldDate & "#" 
endif
' test or filter has been filled:
IF len(strFilter) > 0 then
    ' filter found, remove first " and " string
    me.subformname.form.filter = mid(strFilter,5)
    me.subformname.form.filteron = True
else
    ' deactivate filter
    me.subformname.form.filteron = False
endif
 

Clear ?

Nic;o)