If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple filters using combo box
Hi,
I'm trying to figure out how to filter a query using the following criteria: Start Date and End Date Plus Issue or All Agent or All Rep or All In each combo box I have the following: SELECT [tbl_Issue].[Issue] FROM tbl_Issue union select ' All' FROM tbl_Issue ORDER BY [Issue]; etc. In the query I have the following: Date: =[Forms]![frmselector]![txtStartDate] And =[Forms]![frmselector]![txtEndDate] Issue: Like [Forms]![frmselector]![Issue] etc. However, I get a blank query. What am I missing? |
#2
|
|||
|
|||
Multiple filters using combo box
I suggest you download a sample database that uses a form built with combos
and textboxes to do the searching. This sample shows you how to do all the code to filter the query and get the records the user wants. Here's the link http://www.allenbrowne.com//ser-62.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... Hi, I'm trying to figure out how to filter a query using the following criteria: Start Date and End Date Plus Issue or All Agent or All Rep or All In each combo box I have the following: SELECT [tbl_Issue].[Issue] FROM tbl_Issue union select ' All' FROM tbl_Issue ORDER BY [Issue]; etc. In the query I have the following: Date: =[Forms]![frmselector]![txtStartDate] And =[Forms]![frmselector]![txtEndDate] Issue: Like [Forms]![frmselector]![Issue] etc. However, I get a blank query. What am I missing? |
#3
|
|||
|
|||
Multiple filters using combo box
Here's another sample database for this, too:
Using Controls to filter a form's data http://www.accessmvp.com/KDSnell/Sam...htm#FilterForm -- Ken Snell http://www.accessmvp.com/KDSnell/ "Jeanette Cunningham" wrote in message ... I suggest you download a sample database that uses a form built with combos and textboxes to do the searching. This sample shows you how to do all the code to filter the query and get the records the user wants. Here's the link http://www.allenbrowne.com//ser-62.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... Hi, I'm trying to figure out how to filter a query using the following criteria: Start Date and End Date Plus Issue or All Agent or All Rep or All In each combo box I have the following: SELECT [tbl_Issue].[Issue] FROM tbl_Issue union select ' All' FROM tbl_Issue ORDER BY [Issue]; etc. In the query I have the following: Date: =[Forms]![frmselector]![txtStartDate] And =[Forms]![frmselector]![txtEndDate] Issue: Like [Forms]![frmselector]![Issue] etc. However, I get a blank query. What am I missing? |
#4
|
|||
|
|||
Multiple filters using combo box
I tried Allen's example, but it's not working.
Only the date filters, which are text fields work. The other filters, which are combo boxes, do not work. PARAMETERS [Forms]![frmselector]![issue] Short, [Forms]![frmselector]![agent] Short, [Forms]![frmselector]![agent] Short, [Forms]![frmselector]![txtStartDate] DateTime, [Forms]![frmselector]![txtEndDate] DateTime; SELECT tbl_Tracker.* FROM tbl_Tracker WHERE ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null)) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent])) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR (((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))); "Jeanette Cunningham" wrote: I suggest you download a sample database that uses a form built with combos and textboxes to do the searching. This sample shows you how to do all the code to filter the query and get the records the user wants. Here's the link http://www.allenbrowne.com//ser-62.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... Hi, I'm trying to figure out how to filter a query using the following criteria: Start Date and End Date Plus Issue or All Agent or All Rep or All In each combo box I have the following: SELECT [tbl_Issue].[Issue] FROM tbl_Issue union select ' All' FROM tbl_Issue ORDER BY [Issue]; etc. In the query I have the following: Date: =[Forms]![frmselector]![txtStartDate] And =[Forms]![frmselector]![txtEndDate] Issue: Like [Forms]![frmselector]![Issue] etc. However, I get a blank query. What am I missing? . |
#5
|
|||
|
|||
Multiple filters using combo box
That code you have posted does not look at all like the code in Allen's
sample search form. To use Allen's code, build a search form and copy Allen's code and change the combo and textbox names to suit the controls on your form. I would also remove the parameters - you don't need to mess with parameters when using a search form - much easier for you and the user. Please explain a bit about frmSelector - is it the name of the form with the combos? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... I tried Allen's example, but it's not working. Only the date filters, which are text fields work. The other filters, which are combo boxes, do not work. PARAMETERS [Forms]![frmselector]![issue] Short, [Forms]![frmselector]![agent] Short, [Forms]![frmselector]![agent] Short, [Forms]![frmselector]![txtStartDate] DateTime, [Forms]![frmselector]![txtEndDate] DateTime; SELECT tbl_Tracker.* FROM tbl_Tracker WHERE ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null)) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent])) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR (((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))); "Jeanette Cunningham" wrote: I suggest you download a sample database that uses a form built with combos and textboxes to do the searching. This sample shows you how to do all the code to filter the query and get the records the user wants. Here's the link http://www.allenbrowne.com//ser-62.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... Hi, I'm trying to figure out how to filter a query using the following criteria: Start Date and End Date Plus Issue or All Agent or All Rep or All In each combo box I have the following: SELECT [tbl_Issue].[Issue] FROM tbl_Issue union select ' All' FROM tbl_Issue ORDER BY [Issue]; etc. In the query I have the following: Date: =[Forms]![frmselector]![txtStartDate] And =[Forms]![frmselector]![txtEndDate] Issue: Like [Forms]![frmselector]![Issue] etc. However, I get a blank query. What am I missing? . |
#6
|
|||
|
|||
Multiple filters using combo box
I used his query option. I want the form to filter the query and then run a
report off of the filtered query. I do not want the filtered results to display on the form. So I didn't use the form code as I'm not that skilled at SQL to know what to use and what not to use. frmSelector is the name of the form I'm useing to filter. Thanks, Whitney "Jeanette Cunningham" wrote: That code you have posted does not look at all like the code in Allen's sample search form. To use Allen's code, build a search form and copy Allen's code and change the combo and textbox names to suit the controls on your form. I would also remove the parameters - you don't need to mess with parameters when using a search form - much easier for you and the user. Please explain a bit about frmSelector - is it the name of the form with the combos? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... I tried Allen's example, but it's not working. Only the date filters, which are text fields work. The other filters, which are combo boxes, do not work. PARAMETERS [Forms]![frmselector]![issue] Short, [Forms]![frmselector]![agent] Short, [Forms]![frmselector]![agent] Short, [Forms]![frmselector]![txtStartDate] DateTime, [Forms]![frmselector]![txtEndDate] DateTime; SELECT tbl_Tracker.* FROM tbl_Tracker WHERE ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null)) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent])) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR (((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))); "Jeanette Cunningham" wrote: I suggest you download a sample database that uses a form built with combos and textboxes to do the searching. This sample shows you how to do all the code to filter the query and get the records the user wants. Here's the link http://www.allenbrowne.com//ser-62.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... Hi, I'm trying to figure out how to filter a query using the following criteria: Start Date and End Date Plus Issue or All Agent or All Rep or All In each combo box I have the following: SELECT [tbl_Issue].[Issue] FROM tbl_Issue union select ' All' FROM tbl_Issue ORDER BY [Issue]; etc. In the query I have the following: Date: =[Forms]![frmselector]![txtStartDate] And =[Forms]![frmselector]![txtEndDate] Issue: Like [Forms]![frmselector]![Issue] etc. However, I get a blank query. What am I missing? . . |
#7
|
|||
|
|||
Multiple filters using combo box
Now I understand where you are coming from.
The way to go is to use the form code, but we make one important change just before the end of it. Instead of showing the records in the form, we use the filter string as the where clause to open the report. As well, you don't need to put any of the textboxes in the detail section of the form - that section will just be empty on your form. The user just sees the combos and a button to run the report. You would use the search button from Allen's example to open the report using the code below. Change the button's caption to something like 'Report' instead of 'Search', you will still need the reset button. What to do? Follow Allen's code and change the end like this If lngLen 0 Then strWHERE = Left$(strWHERE, lngLen) 'Debug.Print strWhere 'Apply the string as the report's where condition DoCmd.OpenReport "ReportName",acViewPreview, ,strWhere End If Note: replace 'ReportName' with the name for your report. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... I used his query option. I want the form to filter the query and then run a report off of the filtered query. I do not want the filtered results to display on the form. So I didn't use the form code as I'm not that skilled at SQL to know what to use and what not to use. frmSelector is the name of the form I'm useing to filter. Thanks, Whitney "Jeanette Cunningham" wrote: That code you have posted does not look at all like the code in Allen's sample search form. To use Allen's code, build a search form and copy Allen's code and change the combo and textbox names to suit the controls on your form. I would also remove the parameters - you don't need to mess with parameters when using a search form - much easier for you and the user. Please explain a bit about frmSelector - is it the name of the form with the combos? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... I tried Allen's example, but it's not working. Only the date filters, which are text fields work. The other filters, which are combo boxes, do not work. PARAMETERS [Forms]![frmselector]![issue] Short, [Forms]![frmselector]![agent] Short, [Forms]![frmselector]![agent] Short, [Forms]![frmselector]![txtStartDate] DateTime, [Forms]![frmselector]![txtEndDate] DateTime; SELECT tbl_Tracker.* FROM tbl_Tracker WHERE ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null)) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent])) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![txtStartDate]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![txtEndDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate])) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![slr]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![txtStartDate]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![slr]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND (([Forms]![frmselector]![agent]) Is Null) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![agent]) Is Null) AND ((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR ((([Forms]![frmselector]![issue]) Is Null) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))) OR (((tbl_Tracker.Issue)=[Forms]![frmselector]![issue]) AND ((tbl_Tracker.Agent)=[Forms]![frmselector]![agent]) AND ((tbl_Tracker.SLR)=[Forms]![frmselector]![slr]) AND ((tbl_Tracker.Date)=[Forms]![frmselector]![txtStartDate] And (tbl_Tracker.Date)DateAdd("d",1,[Forms]![frmselector]![txtEndDate]))); "Jeanette Cunningham" wrote: I suggest you download a sample database that uses a form built with combos and textboxes to do the searching. This sample shows you how to do all the code to filter the query and get the records the user wants. Here's the link http://www.allenbrowne.com//ser-62.html Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Whitney" wrote in message ... Hi, I'm trying to figure out how to filter a query using the following criteria: Start Date and End Date Plus Issue or All Agent or All Rep or All In each combo box I have the following: SELECT [tbl_Issue].[Issue] FROM tbl_Issue union select ' All' FROM tbl_Issue ORDER BY [Issue]; etc. In the query I have the following: Date: =[Forms]![frmselector]![txtStartDate] And =[Forms]![frmselector]![txtEndDate] Issue: Like [Forms]![frmselector]![Issue] etc. However, I get a blank query. What am I missing? . . |
Thread Tools | |
Display Modes | |
|
|