Multiple Query Critieria with Multiple Combo Boxes

A

Angelsnecropolis

I've searched through many questions already with no luck finding the answer.

I have a Query and Report by the name of Call Types.
I have 2 combo boxes (Replist, Mgrlist) on a form (Helpgate Menu).

I'm trying to get it so that I can load the report from data in the
"replist" or the "mgrlist", but not both, or if left blank it provides
results for all the reps in the replist.

I can get it to work with just one criteria using [forms]![Helpgate
Menu]![Replist].
I don't have much Access knowledge and I'm learning as I go.
Help me out please?
 
A

Allen Browne

The best way to do this is to add a command button to your Helpgate Menu
form, to open the report. Leave the criteria out of the query; instead use
OpenReport in the button's Click event procedure to open the report, with
the WhereCondition to limit the report. You can build the WhereCondition
string from those boxes where the user entered something.

Here's an example of how to build such a string:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example shows out to build the filter string from several boxes, for
different data types. It applies the filter to the form, but the technique
is identical for a report.

If you don't want to do that, it is possible to edit the SQL View of the
query directly so the WHERE clause evaluates to True if the combos are left
blank. This is clumsy to maintain, messy to add more criteria to,
inefficient to execute, and fails to address your design goal that the user
can't do both, but if you want to go that way it would end up something like
this:
SELECT ...
FROM ...
WHERE (([forms]![Helpgate Menu]![Replist] Is Null)
OR (replist = [forms]![Helpgate Menu]![Replist]))
AND (([forms]![Helpgate Menu]![Mgrlist] Is Null)
OR (mgrlist = [forms]![Helpgate Menu]![Mgrlist]))


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
A

Angelsnecropolis

Thank you Mr. Browne. I was able to tweak the SQL you provided to meet the
needs of the form and it's working. I appreciate your assistance ^_^

Allen Browne said:
The best way to do this is to add a command button to your Helpgate Menu
form, to open the report. Leave the criteria out of the query; instead use
OpenReport in the button's Click event procedure to open the report, with
the WhereCondition to limit the report. You can build the WhereCondition
string from those boxes where the user entered something.

Here's an example of how to build such a string:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example shows out to build the filter string from several boxes, for
different data types. It applies the filter to the form, but the technique
is identical for a report.

If you don't want to do that, it is possible to edit the SQL View of the
query directly so the WHERE clause evaluates to True if the combos are left
blank. This is clumsy to maintain, messy to add more criteria to,
inefficient to execute, and fails to address your design goal that the user
can't do both, but if you want to go that way it would end up something like
this:
SELECT ...
FROM ...
WHERE (([forms]![Helpgate Menu]![Replist] Is Null)
OR (replist = [forms]![Helpgate Menu]![Replist]))
AND (([forms]![Helpgate Menu]![Mgrlist] Is Null)
OR (mgrlist = [forms]![Helpgate Menu]![Mgrlist]))


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I've searched through many questions already with no luck finding the
answer.

I have a Query and Report by the name of Call Types.
I have 2 combo boxes (Replist, Mgrlist) on a form (Helpgate Menu).

I'm trying to get it so that I can load the report from data in the
"replist" or the "mgrlist", but not both, or if left blank it provides
results for all the reps in the replist.

I can get it to work with just one criteria using [forms]![Helpgate
Menu]![Replist].
I don't have much Access knowledge and I'm learning as I go.
Help me out please?
Click to expand...
Click to expand...
 
A

Angelsnecropolis

I ran into a snag Mr. Browne.

The main query I have (Helpgate Tracking) has 3 fields for Questions,
Takeovers, & Escalations. When I click on a checkbox in a form it puts an X
in one of the 3 corresponding query fields. Another query for each of the 3
seperate fields exists. On the form (Helpgate Menu) when Replist & Mgrlist
are blank the report correctly returns the results for the Corresponding
query (Questions, Takeovers, Escalations). However, if anything is in the
Replist or Mgrlist fields then the query results are not specific to each
type but combines all 3. Any clues?

SQL:
SELECT [Helpgate Tracking].Date, [Helpgate Tracking].[C/S Rep ID], [Helpgate
Tracking].Notes, [Helpgate Tracking].[Inquiry Type], [Helpgate
Tracking].Escalation, Managers.Mgr
FROM (Managers INNER JOIN [Rep List] ON Managers.Mgr=[Rep List].Mgr) INNER
JOIN [Helpgate Tracking] ON [Rep List].[C/S Rep ID]=[Helpgate Tracking].[C/S
Rep ID]
WHERE ((([Helpgate Tracking].Date) Between Forms![Helpgate Menu]!Date1 And
Forms![Helpgate Menu]!Date2) And (([Helpgate
Tracking].Escalation)=[Escalation]) And ((forms![Helpgate Menu]!Replist) Is
Null) And ((forms![Helpgate Menu]!Mgrlist) Is Null)) Or ((([Helpgate
Tracking].Date) Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate
Menu]!Date2) And ((forms![Helpgate Menu]!Mgrlist) Is Null) And (([Rep
List].[C/S Rep ID])=forms![Helpgate Menu]!Replist)) Or ((([Helpgate
Tracking].Date) Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate
Menu]!Date2) And ((Managers.Mgr)=forms![Helpgate Menu]!Mgrlist) And
((forms![Helpgate Menu]!Replist) Is Null)) Or ((([Helpgate Tracking].Date)
Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate Menu]!Date2) And
((Managers.Mgr)=forms![Helpgate Menu]!Mgrlist) And (([Rep List].[C/S Rep
ID])=forms![Helpgate Menu]!Replist));

Allen Browne said:
The best way to do this is to add a command button to your Helpgate Menu
form, to open the report. Leave the criteria out of the query; instead use
OpenReport in the button's Click event procedure to open the report, with
the WhereCondition to limit the report. You can build the WhereCondition
string from those boxes where the user entered something.

Here's an example of how to build such a string:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example shows out to build the filter string from several boxes, for
different data types. It applies the filter to the form, but the technique
is identical for a report.

If you don't want to do that, it is possible to edit the SQL View of the
query directly so the WHERE clause evaluates to True if the combos are left
blank. This is clumsy to maintain, messy to add more criteria to,
inefficient to execute, and fails to address your design goal that the user
can't do both, but if you want to go that way it would end up something like
this:
SELECT ...
FROM ...
WHERE (([forms]![Helpgate Menu]![Replist] Is Null)
OR (replist = [forms]![Helpgate Menu]![Replist]))
AND (([forms]![Helpgate Menu]![Mgrlist] Is Null)
OR (mgrlist = [forms]![Helpgate Menu]![Mgrlist]))


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I've searched through many questions already with no luck finding the
answer.

I have a Query and Report by the name of Call Types.
I have 2 combo boxes (Replist, Mgrlist) on a form (Helpgate Menu).

I'm trying to get it so that I can load the report from data in the
"replist" or the "mgrlist", but not both, or if left blank it provides
results for all the reps in the replist.

I can get it to work with just one criteria using [forms]![Helpgate
Menu]![Replist].
I don't have much Access knowledge and I'm learning as I go.
Help me out please?
Click to expand...
Click to expand...
 
A

Allen Browne

I think that's getting beyond what I can help you with in a newsgroup
posting.

It may be possible to develop stacked queries (a query that uses another as
an input 'table') or subqueries (a query that contains another SELECT query
statement) to identify the problem records here. Possibly a Totals query
where the count of primary key is 3. But I can't guide you through the
implementation of those suggestions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I ran into a snag Mr. Browne.

The main query I have (Helpgate Tracking) has 3 fields for Questions,
Takeovers, & Escalations. When I click on a checkbox in a form it puts an
X
in one of the 3 corresponding query fields. Another query for each of the
3
seperate fields exists. On the form (Helpgate Menu) when Replist & Mgrlist
are blank the report correctly returns the results for the Corresponding
query (Questions, Takeovers, Escalations). However, if anything is in the
Replist or Mgrlist fields then the query results are not specific to each
type but combines all 3. Any clues?

SQL:
SELECT [Helpgate Tracking].Date, [Helpgate Tracking].[C/S Rep ID],
[Helpgate
Tracking].Notes, [Helpgate Tracking].[Inquiry Type], [Helpgate
Tracking].Escalation, Managers.Mgr
FROM (Managers INNER JOIN [Rep List] ON Managers.Mgr=[Rep List].Mgr) INNER
JOIN [Helpgate Tracking] ON [Rep List].[C/S Rep ID]=[Helpgate
Tracking].[C/S
Rep ID]
WHERE ((([Helpgate Tracking].Date) Between Forms![Helpgate Menu]!Date1 And
Forms![Helpgate Menu]!Date2) And (([Helpgate
Tracking].Escalation)=[Escalation]) And ((forms![Helpgate Menu]!Replist)
Is
Null) And ((forms![Helpgate Menu]!Mgrlist) Is Null)) Or ((([Helpgate
Tracking].Date) Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate
Menu]!Date2) And ((forms![Helpgate Menu]!Mgrlist) Is Null) And (([Rep
List].[C/S Rep ID])=forms![Helpgate Menu]!Replist)) Or ((([Helpgate
Tracking].Date) Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate
Menu]!Date2) And ((Managers.Mgr)=forms![Helpgate Menu]!Mgrlist) And
((forms![Helpgate Menu]!Replist) Is Null)) Or ((([Helpgate Tracking].Date)
Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate Menu]!Date2) And
((Managers.Mgr)=forms![Helpgate Menu]!Mgrlist) And (([Rep List].[C/S Rep
ID])=forms![Helpgate Menu]!Replist));

Allen Browne said:
The best way to do this is to add a command button to your Helpgate Menu
form, to open the report. Leave the criteria out of the query; instead
use
OpenReport in the button's Click event procedure to open the report, with
the WhereCondition to limit the report. You can build the WhereCondition
string from those boxes where the user entered something.

Here's an example of how to build such a string:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example shows out to build the filter string from several boxes, for
different data types. It applies the filter to the form, but the
technique
is identical for a report.

If you don't want to do that, it is possible to edit the SQL View of the
query directly so the WHERE clause evaluates to True if the combos are
left
blank. This is clumsy to maintain, messy to add more criteria to,
inefficient to execute, and fails to address your design goal that the
user
can't do both, but if you want to go that way it would end up something
like
this:
SELECT ...
FROM ...
WHERE (([forms]![Helpgate Menu]![Replist] Is Null)
OR (replist = [forms]![Helpgate Menu]![Replist]))
AND (([forms]![Helpgate Menu]![Mgrlist] Is Null)
OR (mgrlist = [forms]![Helpgate Menu]![Mgrlist]))


message
I've searched through many questions already with no luck finding the
answer.

I have a Query and Report by the name of Call Types.
I have 2 combo boxes (Replist, Mgrlist) on a form (Helpgate Menu).

I'm trying to get it so that I can load the report from data in the
"replist" or the "mgrlist", but not both, or if left blank it provides
results for all the reps in the replist.

I can get it to work with just one criteria using [forms]![Helpgate
Menu]![Replist].
I don't have much Access knowledge and I'm learning as I go.
Help me out please?
Click to expand...
Click to expand...
Click to expand...
 
A

Angelsnecropolis

From your description I am using stacked queries. I have 3 seperate queries
for each group that feeds from a main queiry. I think the problem is that
it's only wanting to return results from one queiry critiera instead of two
critierias. It'll give me all the "escalations" or all the results from a
specific "Manager" but not all the escalations from a specific manager (both).

I suppose I'll have to do some more research on google at home. Can't google
while at work =\

Thank you for your help.

Allen Browne said:
I think that's getting beyond what I can help you with in a newsgroup
posting.

It may be possible to develop stacked queries (a query that uses another as
an input 'table') or subqueries (a query that contains another SELECT query
statement) to identify the problem records here. Possibly a Totals query
where the count of primary key is 3. But I can't guide you through the
implementation of those suggestions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I ran into a snag Mr. Browne.

The main query I have (Helpgate Tracking) has 3 fields for Questions,
Takeovers, & Escalations. When I click on a checkbox in a form it puts an
X
in one of the 3 corresponding query fields. Another query for each of the
3
seperate fields exists. On the form (Helpgate Menu) when Replist & Mgrlist
are blank the report correctly returns the results for the Corresponding
query (Questions, Takeovers, Escalations). However, if anything is in the
Replist or Mgrlist fields then the query results are not specific to each
type but combines all 3. Any clues?

SQL:
SELECT [Helpgate Tracking].Date, [Helpgate Tracking].[C/S Rep ID],
[Helpgate
Tracking].Notes, [Helpgate Tracking].[Inquiry Type], [Helpgate
Tracking].Escalation, Managers.Mgr
FROM (Managers INNER JOIN [Rep List] ON Managers.Mgr=[Rep List].Mgr) INNER
JOIN [Helpgate Tracking] ON [Rep List].[C/S Rep ID]=[Helpgate
Tracking].[C/S
Rep ID]
WHERE ((([Helpgate Tracking].Date) Between Forms![Helpgate Menu]!Date1 And
Forms![Helpgate Menu]!Date2) And (([Helpgate
Tracking].Escalation)=[Escalation]) And ((forms![Helpgate Menu]!Replist)
Is
Null) And ((forms![Helpgate Menu]!Mgrlist) Is Null)) Or ((([Helpgate
Tracking].Date) Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate
Menu]!Date2) And ((forms![Helpgate Menu]!Mgrlist) Is Null) And (([Rep
List].[C/S Rep ID])=forms![Helpgate Menu]!Replist)) Or ((([Helpgate
Tracking].Date) Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate
Menu]!Date2) And ((Managers.Mgr)=forms![Helpgate Menu]!Mgrlist) And
((forms![Helpgate Menu]!Replist) Is Null)) Or ((([Helpgate Tracking].Date)
Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate Menu]!Date2) And
((Managers.Mgr)=forms![Helpgate Menu]!Mgrlist) And (([Rep List].[C/S Rep
ID])=forms![Helpgate Menu]!Replist));

Allen Browne said:
The best way to do this is to add a command button to your Helpgate Menu
form, to open the report. Leave the criteria out of the query; instead
use
OpenReport in the button's Click event procedure to open the report, with
the WhereCondition to limit the report. You can build the WhereCondition
string from those boxes where the user entered something.

Here's an example of how to build such a string:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example shows out to build the filter string from several boxes, for
different data types. It applies the filter to the form, but the
technique
is identical for a report.

If you don't want to do that, it is possible to edit the SQL View of the
query directly so the WHERE clause evaluates to True if the combos are
left
blank. This is clumsy to maintain, messy to add more criteria to,
inefficient to execute, and fails to address your design goal that the
user
can't do both, but if you want to go that way it would end up something
like
this:
SELECT ...
FROM ...
WHERE (([forms]![Helpgate Menu]![Replist] Is Null)
OR (replist = [forms]![Helpgate Menu]![Replist]))
AND (([forms]![Helpgate Menu]![Mgrlist] Is Null)
OR (mgrlist = [forms]![Helpgate Menu]![Mgrlist]))


message
I've searched through many questions already with no luck finding the
answer.

I have a Query and Report by the name of Call Types.
I have 2 combo boxes (Replist, Mgrlist) on a form (Helpgate Menu).

I'm trying to get it so that I can load the report from data in the
"replist" or the "mgrlist", but not both, or if left blank it provides
results for all the reps in the replist.

I can get it to work with just one criteria using [forms]![Helpgate
Menu]![Replist].
I don't have much Access knowledge and I'm learning as I go.
Help me out please?
Click to expand...
Click to expand...
Click to expand...
 
A

Angelsnecropolis

After some additional tweaking I figured it out. In query design I had to add
columns for the 2 results I didnt want to display, uncheck show, and in Or: I
added Is Null.

I couldn't have figured it out without your help though ^_^

Allen Browne said:
I think that's getting beyond what I can help you with in a newsgroup
posting.

It may be possible to develop stacked queries (a query that uses another as
an input 'table') or subqueries (a query that contains another SELECT query
statement) to identify the problem records here. Possibly a Totals query
where the count of primary key is 3. But I can't guide you through the
implementation of those suggestions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I ran into a snag Mr. Browne.

The main query I have (Helpgate Tracking) has 3 fields for Questions,
Takeovers, & Escalations. When I click on a checkbox in a form it puts an
X
in one of the 3 corresponding query fields. Another query for each of the
3
seperate fields exists. On the form (Helpgate Menu) when Replist & Mgrlist
are blank the report correctly returns the results for the Corresponding
query (Questions, Takeovers, Escalations). However, if anything is in the
Replist or Mgrlist fields then the query results are not specific to each
type but combines all 3. Any clues?

SQL:
SELECT [Helpgate Tracking].Date, [Helpgate Tracking].[C/S Rep ID],
[Helpgate
Tracking].Notes, [Helpgate Tracking].[Inquiry Type], [Helpgate
Tracking].Escalation, Managers.Mgr
FROM (Managers INNER JOIN [Rep List] ON Managers.Mgr=[Rep List].Mgr) INNER
JOIN [Helpgate Tracking] ON [Rep List].[C/S Rep ID]=[Helpgate
Tracking].[C/S
Rep ID]
WHERE ((([Helpgate Tracking].Date) Between Forms![Helpgate Menu]!Date1 And
Forms![Helpgate Menu]!Date2) And (([Helpgate
Tracking].Escalation)=[Escalation]) And ((forms![Helpgate Menu]!Replist)
Is
Null) And ((forms![Helpgate Menu]!Mgrlist) Is Null)) Or ((([Helpgate
Tracking].Date) Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate
Menu]!Date2) And ((forms![Helpgate Menu]!Mgrlist) Is Null) And (([Rep
List].[C/S Rep ID])=forms![Helpgate Menu]!Replist)) Or ((([Helpgate
Tracking].Date) Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate
Menu]!Date2) And ((Managers.Mgr)=forms![Helpgate Menu]!Mgrlist) And
((forms![Helpgate Menu]!Replist) Is Null)) Or ((([Helpgate Tracking].Date)
Between Forms![Helpgate Menu]!Date1 And Forms![Helpgate Menu]!Date2) And
((Managers.Mgr)=forms![Helpgate Menu]!Mgrlist) And (([Rep List].[C/S Rep
ID])=forms![Helpgate Menu]!Replist));

Allen Browne said:
The best way to do this is to add a command button to your Helpgate Menu
form, to open the report. Leave the criteria out of the query; instead
use
OpenReport in the button's Click event procedure to open the report, with
the WhereCondition to limit the report. You can build the WhereCondition
string from those boxes where the user entered something.

Here's an example of how to build such a string:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example shows out to build the filter string from several boxes, for
different data types. It applies the filter to the form, but the
technique
is identical for a report.

If you don't want to do that, it is possible to edit the SQL View of the
query directly so the WHERE clause evaluates to True if the combos are
left
blank. This is clumsy to maintain, messy to add more criteria to,
inefficient to execute, and fails to address your design goal that the
user
can't do both, but if you want to go that way it would end up something
like
this:
SELECT ...
FROM ...
WHERE (([forms]![Helpgate Menu]![Replist] Is Null)
OR (replist = [forms]![Helpgate Menu]![Replist]))
AND (([forms]![Helpgate Menu]![Mgrlist] Is Null)
OR (mgrlist = [forms]![Helpgate Menu]![Mgrlist]))


message
I've searched through many questions already with no luck finding the
answer.

I have a Query and Report by the name of Call Types.
I have 2 combo boxes (Replist, Mgrlist) on a form (Helpgate Menu).

I'm trying to get it so that I can load the report from data in the
"replist" or the "mgrlist", but not both, or if left blank it provides
results for all the reps in the replist.

I can get it to work with just one criteria using [forms]![Helpgate
Menu]![Replist].
I don't have much Access knowledge and I'm learning as I go.
Help me out please?
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

Multiple IFF Statements help 3
Whats wrong with this code? 7
Object doesnt support this property... 1
Expression for Criteria for Form with Combo Boxes 1
Multiple search Criteria 5
query relelated to 2 combo boxes 2
Multiple Combo Boxes Limiting a Query 5
Query with Combo Boxes on a Form 1

Top