Am I using too many filters?

G

Guest

For some reports, I prefer to write a single report and give the user up to
ten or more filters (controls, usually combo boxes) on the form that calls
the report). I then drop a reference to the control name in the query's
criteria line to filter based on that control if it is populated.

Since I have, by default, only nine criteria lines to a query, I sometimes
create two or three queries that each filter out different elements, and then
do a join between them to apply ALL filters.

However, I just ran into my first need to use a list box for one of the
criteria (or at least a need to stipulate "Equals" or "Does not equal" as an
option), and the construction of the resultant query in VBA looks like a
garganuan task. Does anyone have a better approach to multi-filtered reports,
or am I just crazy to try to offer this type of flexibility?

Just as an example, here is the SQL view of one of three such stacked
queries that it takes to run one VERY flexible report, and yes, I did this
with the query builder, not by manually writing the SQL:

SELECT Contracts.ContractID
FROM ReportContractBase1 INNER JOIN Contracts ON
ReportContractBase1.ContractID = Contracts.ContractID
WHERE (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
((([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null));
 
D

Duane Hookom

I try to create reports (record source queries) with no criterias. Instead,
I use code in the form "MenuReports" to create a where string that is used
in the DoCmd.OpenReport method. For instance:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.ContractID) Then
'assuming ContractID is numeric
strWhere = strWhere & " And ContractID = " & Me.ContractID
End If
If Not IsNull(Me.ContractType) Then
'assuming ContractType is text
strWhere = strWhere & " And ContractType = """ & Me.ContractType &
""" "
End If
'--- etc -----
'you can add code that loops through a multi-select list box
'this makes date ranges easy
'the only major issue is you can't apply this technique very easily
' to subreports
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP


Brian said:
For some reports, I prefer to write a single report and give the user up
to
ten or more filters (controls, usually combo boxes) on the form that calls
the report). I then drop a reference to the control name in the query's
criteria line to filter based on that control if it is populated.

Since I have, by default, only nine criteria lines to a query, I sometimes
create two or three queries that each filter out different elements, and
then
do a join between them to apply ALL filters.

However, I just ran into my first need to use a list box for one of the
criteria (or at least a need to stipulate "Equals" or "Does not equal" as
an
option), and the construction of the resultant query in VBA looks like a
garganuan task. Does anyone have a better approach to multi-filtered
reports,
or am I just crazy to try to offer this type of flexibility?

Just as an example, here is the SQL view of one of three such stacked
queries that it takes to run one VERY flexible report, and yes, I did this
with the query builder, not by manually writing the SQL:

SELECT Contracts.ContractID
FROM ReportContractBase1 INNER JOIN Contracts ON
ReportContractBase1.ContractID = Contracts.ContractID
WHERE (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
((([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null));
Click to expand...
 
S

Sylvain Lafontaine

You have answered yourself to your own question:

« ... and yes, I did this with the query builder, not by manually
writing the SQL »

What kind of answer are you expecting from us?

S. L.

Brian said:
For some reports, I prefer to write a single report and give the user up
to
ten or more filters (controls, usually combo boxes) on the form that calls
the report). I then drop a reference to the control name in the query's
criteria line to filter based on that control if it is populated.

Since I have, by default, only nine criteria lines to a query, I sometimes
create two or three queries that each filter out different elements, and
then
do a join between them to apply ALL filters.

However, I just ran into my first need to use a list box for one of the
criteria (or at least a need to stipulate "Equals" or "Does not equal" as
an
option), and the construction of the resultant query in VBA looks like a
garganuan task. Does anyone have a better approach to multi-filtered
reports,
or am I just crazy to try to offer this type of flexibility?

Just as an example, here is the SQL view of one of three such stacked
queries that it takes to run one VERY flexible report, and yes, I did this
with the query builder, not by manually writing the SQL:

SELECT Contracts.ContractID
FROM ReportContractBase1 INNER JOIN Contracts ON
ReportContractBase1.ContractID = Contracts.ContractID
WHERE (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
((([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null));
Click to expand...
 
G

Guest

I started down the Control.Selected / Control.ItemData path, but the thought
of attempting to concatenate the output from upward of ten such loops (since
I am allowing the user to filter by tht many separate fields in the tables
involved) began to look rather daunting. However, that is probably just
because I have never taken the time to really break down the SQL to its
individual elements. Seeing the breakdown actually makes it a lot more
flexible & simple than attempting to press it all into query criteria
(IF...Then loops are certainly easier to use than IIf's in a query).

Thanks for the breakdown.


Duane Hookom said:
I try to create reports (record source queries) with no criterias. Instead,
I use code in the form "MenuReports" to create a where string that is used
in the DoCmd.OpenReport method. For instance:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.ContractID) Then
'assuming ContractID is numeric
strWhere = strWhere & " And ContractID = " & Me.ContractID
End If
If Not IsNull(Me.ContractType) Then
'assuming ContractType is text
strWhere = strWhere & " And ContractType = """ & Me.ContractType &
""" "
End If
'--- etc -----
'you can add code that loops through a multi-select list box
'this makes date ranges easy
'the only major issue is you can't apply this technique very easily
' to subreports
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP


Brian said:
For some reports, I prefer to write a single report and give the user up
to
ten or more filters (controls, usually combo boxes) on the form that calls
the report). I then drop a reference to the control name in the query's
criteria line to filter based on that control if it is populated.

Since I have, by default, only nine criteria lines to a query, I sometimes
create two or three queries that each filter out different elements, and
then
do a join between them to apply ALL filters.

However, I just ran into my first need to use a list box for one of the
criteria (or at least a need to stipulate "Equals" or "Does not equal" as
an
option), and the construction of the resultant query in VBA looks like a
garganuan task. Does anyone have a better approach to multi-filtered
reports,
or am I just crazy to try to offer this type of flexibility?

Just as an example, here is the SQL view of one of three such stacked
queries that it takes to run one VERY flexible report, and yes, I did this
with the query builder, not by manually writing the SQL:

SELECT Contracts.ContractID
FROM ReportContractBase1 INNER JOIN Contracts ON
ReportContractBase1.ContractID = Contracts.ContractID
WHERE (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
((([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null));
Click to expand...
Click to expand...
 
G

Guest

Actually, I was just wondering if there are better ways to approach reporting
than writing very basic reports and giving users the choice of many filters.
I think the concept of concatenating the various critieria via strings into
the Where string probably allows more flexibility than my purely
query-builder approach.

Thanks.

Sylvain Lafontaine said:
You have answered yourself to your own question:

« ... and yes, I did this with the query builder, not by manually
writing the SQL »

What kind of answer are you expecting from us?

S. L.

Brian said:
For some reports, I prefer to write a single report and give the user up
to
ten or more filters (controls, usually combo boxes) on the form that calls
the report). I then drop a reference to the control name in the query's
criteria line to filter based on that control if it is populated.

Since I have, by default, only nine criteria lines to a query, I sometimes
create two or three queries that each filter out different elements, and
then
do a join between them to apply ALL filters.

However, I just ran into my first need to use a list box for one of the
criteria (or at least a need to stipulate "Equals" or "Does not equal" as
an
option), and the construction of the resultant query in VBA looks like a
garganuan task. Does anyone have a better approach to multi-filtered
reports,
or am I just crazy to try to offer this type of flexibility?

Just as an example, here is the SQL view of one of three such stacked
queries that it takes to run one VERY flexible report, and yes, I did this
with the query builder, not by manually writing the SQL:

SELECT Contracts.ContractID
FROM ReportContractBase1 INNER JOIN Contracts ON
ReportContractBase1.ContractID = Contracts.ContractID
WHERE (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
((([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null));
Click to expand...
Click to expand...
 
D

Duane Hookom

Try this Google search to find generic code I wrote for using list boxes
http://groups-beta.google.com/groups?as_q=Hookom+BuildIn&as_ugroup=*access*

--
Duane Hookom
MS Access MVP


Brian said:
I started down the Control.Selected / Control.ItemData path, but the
thought
of attempting to concatenate the output from upward of ten such loops
(since
I am allowing the user to filter by tht many separate fields in the tables
involved) began to look rather daunting. However, that is probably just
because I have never taken the time to really break down the SQL to its
individual elements. Seeing the breakdown actually makes it a lot more
flexible & simple than attempting to press it all into query criteria
(IF...Then loops are certainly easier to use than IIf's in a query).

Thanks for the breakdown.


Duane Hookom said:
I try to create reports (record source queries) with no criterias.
Instead,
I use code in the form "MenuReports" to create a where string that is
used
in the DoCmd.OpenReport method. For instance:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.ContractID) Then
'assuming ContractID is numeric
strWhere = strWhere & " And ContractID = " & Me.ContractID
End If
If Not IsNull(Me.ContractType) Then
'assuming ContractType is text
strWhere = strWhere & " And ContractType = """ & Me.ContractType
&
""" "
End If
'--- etc -----
'you can add code that loops through a multi-select list box
'this makes date ranges easy
'the only major issue is you can't apply this technique very easily
' to subreports
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP


Brian said:
For some reports, I prefer to write a single report and give the user
up
to
ten or more filters (controls, usually combo boxes) on the form that
calls
the report). I then drop a reference to the control name in the
query's
criteria line to filter based on that control if it is populated.

Since I have, by default, only nine criteria lines to a query, I
sometimes
create two or three queries that each filter out different elements,
and
then
do a join between them to apply ALL filters.

However, I just ran into my first need to use a list box for one of the
criteria (or at least a need to stipulate "Equals" or "Does not equal"
as
an
option), and the construction of the resultant query in VBA looks like
a
garganuan task. Does anyone have a better approach to multi-filtered
reports,
or am I just crazy to try to offer this type of flexibility?

Just as an example, here is the SQL view of one of three such stacked
queries that it takes to run one VERY flexible report, and yes, I did
this
with the query builder, not by manually writing the SQL:

SELECT Contracts.ContractID
FROM ReportContractBase1 INNER JOIN Contracts ON
ReportContractBase1.ContractID = Contracts.ContractID
WHERE (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
((([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null));
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

Access Determine what query to run based on a checkbox 0
Multiple Parameter Crosstab query filter by form 1
Using Crosstab column headings for conditional formatting in report 0
Complex Criteria Parameter Query 2
Query is too Complex error message when trying to run query. 3
QUERY MULTIPLE CRITERIA 2
Characters found at end of sql error-Repost 2
Flexible query 1

Top