View Single Post
  #6  
Old February 11th, 2010, 09:43 PM posted to microsoft.public.access.forms
Whitney
external usenet poster
 
Posts: 126
Default 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?


.



.