By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,719 Members | 1,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,719 IT Pros & Developers. It's quick & easy.

Query that will search for mulitiple criteria in mulitiple combinations

P: 5
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?
Jan 18 '07 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 218
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...

Expand|Select|Wrap|Line Numbers
  1. dim strSQL as String
  2. strSQL = "Select * From tblName Where "
  3. If Not IsNull(Form1!FldA) Then
  4.     strSQL = strSQL & "tblName.FldX = " & Form1!FldA
  5. End If
and so on

HTH

Steve
Jan 18 '07 #2

P: 5
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!
Jan 18 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 20 '07 #4

P: 5
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!
Jan 21 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. dim strFilter as string
  2.  
  3. ' empty string
  4. strFilter = ""
  5. ' test text field filled
  6. IF len(nz(me.txtFieldText)) > 0 then
  7.     strFilter = strFilter & " and FieldText='" & me.txtFieldText & "'"
  8. endif
  9. ' test numeric field filled
  10. IF len(nz(me.txtFieldNum)) > 0 then
  11.     strFilter = strFilter & " and FieldNum=" & me.txtFieldNum 
  12. endif
  13. ' test date field filled
  14. IF len(nz(me.txtFieldDate)) > 0 then
  15.     strFilter = strFilter & " and FieldDate=#" & me.txtFieldDate & "#" 
  16. endif
  17. ' test or filter has been filled:
  18. IF len(strFilter) > 0 then
  19.     ' filter found, remove first " and " string
  20.     me.subformname.form.filter = mid(strFilter,5)
  21.     me.subformname.form.filteron = True
  22. else
  23.     ' deactivate filter
  24.     me.subformname.form.filteron = False
  25. endif
  26.  
Clear ?

Nic;o)
Jan 21 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.