Multiple filters using combo box

W

Whitney

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?
 
J

Jeanette Cunningham

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
 
K

Ken Snell

Here's another sample database for this, too:

Using Controls to filter a form's data
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Jeanette Cunningham said:
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 said:
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?
Click to expand...
Click to expand...
 
W

Whitney

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 said:
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 said:
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?
Click to expand...


.
Click to expand...
 
J

Jeanette Cunningham

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 said:
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 said:
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 said:
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?
Click to expand...


.
Click to expand...
Click to expand...
 
W

Whitney

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 said:
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 said:
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 said:
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



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?


.
Click to expand...
Click to expand...


.
Click to expand...
 
J

Jeanette Cunningham

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 said:
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 said:
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 said:
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])));


:

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



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?


.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Putting a CheckBox next to each item in a combo box 8
form using parameter query 3
Filter tabular form by combo box 0
Parameterised query to return all records or those Between two val 2
auto populate text box based on combo box value 1
Can't Get "ALL" to work in a query - Please Help 3
Start and End Dates not appearing in text box of report 1
Parameter Form 4

Top