Combo Box Select All

  • Thread starter Thread starter Jani
  • Start date Start date
J

Jani

So my second question of the day. For a combo box, how does one select all?
I've seen it somewhere using a * but can't find it and am in a bit of a time
crunch. Thanks! Jani
 
What are you trying to do?

If you're using the combo box as a parameter of a query, for instance, you
can add "All" to the combo box (see
http://www.mvps.org/access/forms/frm0043.htm at "The Access Way" for one
way).

You'd then change the criteria of your query from

Field1 = Forms![NameOfForm]![NameOfCombo]

to

(Field1 = Forms![NameOfForm]![NameOfCombo]) OR
(Forms![NameOfForm]![NameOfCombo] IS NULL)
 
Jani said:
So my second question of the day. For a combo box, how does one select
all?
I've seen it somewhere using a * but can't find it and am in a bit of a
time
crunch. Thanks! Jani
Click to expand...


A combo box can only ever have (at most) one item selected at a time. You
can add an item to the combo box's rowsource to be interpreted as "select
all", assuming that you are then going to use that value in code that will
interpret it correctly. Please give more information about what you're
trying to do with the combo box, and maybe we can give you more specific
advice.
 
Select all in a combo box? I assume you don't mean selecting all the items
in the dropdown list, because that cannot be done with a combo box.

If you want the ability to select an item in the dropdown list that will
*mean* you want a query using the combo box as a criterion to return all
records, the easiest way is to leave the combo box empty and change your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to mean "all", you
would need to change your combobox's RowSource query to a UNION query that
includes a record that means all. It'll be easier for us to assist with this
if you post the combobox's RowSource query. But let's assume that you have
two values in that query: an ID field and a Desc field. The new query would
be this:

SELECT ID, Desc FROM TableName
UNION ALL
SELECT DISTINCT NULL AS NullID, "ALL" AS NullDesc
FROM TableName;


Then the query criteria clause that I gave above also will work.
 
So you 3 great guys all say I didn't provide enough information so I will now
try and give better, more thorough information (and you are so right when I
relook at my problem!). I have a combo box based on a query that pulls
employees from a table based on a beginning and end date. A query is used in
a report which displays information about a maintenance issue. I use a query
to reduce the number of employees in the combo box list so one doesn't pick
employee after employee that did not do any maintenance work for the period
of time chosen. What I want to do is have the ability in the report to
display records for all employees if one chooses to do so rather than just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION, dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION, dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location]) AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND ((dbo_uCMMSNonPMTasks.[COMP DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report :
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP LIST].EQUIPMENT =
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null));

Thanks!!!!
 
Change the WHERE clause of the report's record source query to this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets of criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Jani said:
So you 3 great guys all say I didn't provide enough information so I will
now
try and give better, more thorough information (and you are so right when
I
relook at my problem!). I have a combo box based on a query that pulls
employees from a table based on a beginning and end date. A query is used
in
a report which displays information about a maintenance issue. I use a
query
to reduce the number of employees in the combo box list so one doesn't
pick
employee after employee that did not do any maintenance work for the
period
of time chosen. What I want to do is have the ability in the report to
display records for all employees if one chooses to do so rather than just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION, dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION, dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location])
AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND ((dbo_uCMMSNonPMTasks.[COMP
DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report :
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null));

Thanks!!!!


Ken Snell said:
Select all in a combo box? I assume you don't mean selecting all the
items
in the dropdown list, because that cannot be done with a combo box.

If you want the ability to select an item in the dropdown list that will
*mean* you want a query using the combo box as a criterion to return all
records, the easiest way is to leave the combo box empty and change your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or
Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to mean "all",
you
would need to change your combobox's RowSource query to a UNION query
that
includes a record that means all. It'll be easier for us to assist with
this
if you post the combobox's RowSource query. But let's assume that you
have
two values in that query: an ID field and a Desc field. The new query
would
be this:

SELECT ID, Desc FROM TableName
UNION ALL
SELECT DISTINCT NULL AS NullID, "ALL" AS NullDesc
FROM TableName;


Then the query criteria clause that I gave above also will work.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
Click to expand...
 
Thanks, Ken... I hope you come back to this issue though. I have changed the
query as shown below. The drop-down box displays only the data for the
associates that have records between the dates, and the report has the
correct records for dates chosen. However, I still don't see a way to select
all associates which have entered maintenance issues for dates selected.
Manager really doesn't want to run off ten reports if wanting to see all
closed issues. Need some additional help if possible. Thanks again! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP LIST].EQUIPMENT =
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


Ken Snell said:
Change the WHERE clause of the report's record source query to this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets of criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Jani said:
So you 3 great guys all say I didn't provide enough information so I will
now
try and give better, more thorough information (and you are so right when
I
relook at my problem!). I have a combo box based on a query that pulls
employees from a table based on a beginning and end date. A query is used
in
a report which displays information about a maintenance issue. I use a
query
to reduce the number of employees in the combo box list so one doesn't
pick
employee after employee that did not do any maintenance work for the
period
of time chosen. What I want to do is have the ability in the report to
display records for all employees if one chooses to do so rather than just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION, dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION, dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location])
AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND ((dbo_uCMMSNonPMTasks.[COMP
DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report :
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null));

Thanks!!!!


Ken Snell said:
Select all in a combo box? I assume you don't mean selecting all the
items
in the dropdown list, because that cannot be done with a combo box.

If you want the ability to select an item in the dropdown list that will
*mean* you want a query using the combo box as a criterion to return all
records, the easiest way is to leave the combo box empty and change your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or
Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to mean "all",
you
would need to change your combobox's RowSource query to a UNION query
that
includes a record that means all. It'll be easier for us to assist with
this
if you post the combobox's RowSource query. But let's assume that you
have
two values in that query: an ID field and a Desc field. The new query
would
be this:

SELECT ID, Desc FROM TableName
UNION ALL
SELECT DISTINCT NULL AS NullID, "ALL" AS NullDesc
FROM TableName;


Then the query criteria clause that I gave above also will work.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So my second question of the day. For a combo box, how does one select
all?
I've seen it somewhere using a * but can't find it and am in a bit of a
time
crunch. Thanks! Jani
Click to expand...
Click to expand...
Click to expand...
 
Do not select anything in the combo box -- leave it "empty" (which means the
combo box has a NULL value in it). Then run the query. All associates will
be returned who have info in the date range selected.

If you really want to have an option in the dropdown list that can be
selected for "ALL", post the specific SQL query statement that you're using
for the combobox itself and we can modify it (and the report query) to do
that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jani said:
Thanks, Ken... I hope you come back to this issue though. I have changed
the
query as shown below. The drop-down box displays only the data for the
associates that have records between the dates, and the report has the
correct records for dates chosen. However, I still don't see a way to
select
all associates which have entered maintenance issues for dates selected.
Manager really doesn't want to run off ten reports if wanting to see all
closed issues. Need some additional help if possible. Thanks again! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


Ken Snell said:
Change the WHERE clause of the report's record source query to this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets of
criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Jani said:
So you 3 great guys all say I didn't provide enough information so I
will
now
try and give better, more thorough information (and you are so right
when
I
relook at my problem!). I have a combo box based on a query that pulls
employees from a table based on a beginning and end date. A query is
used
in
a report which displays information about a maintenance issue. I use a
query
to reduce the number of employees in the combo box list so one doesn't
pick
employee after employee that did not do any maintenance work for the
period
of time chosen. What I want to do is have the ability in the report to
display records for all employees if one chooses to do so rather than
just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION, dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location])
AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND ((dbo_uCMMSNonPMTasks.[COMP
DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report :
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null));

Thanks!!!!


:

Select all in a combo box? I assume you don't mean selecting all the
items
in the dropdown list, because that cannot be done with a combo box.

If you want the ability to select an item in the dropdown list that
will
*mean* you want a query using the combo box as a criterion to return
all
records, the easiest way is to leave the combo box empty and change
your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or
Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to mean
"all",
you
would need to change your combobox's RowSource query to a UNION query
that
includes a record that means all. It'll be easier for us to assist
with
this
if you post the combobox's RowSource query. But let's assume that you
have
two values in that query: an ID field and a Desc field. The new query
would
be this:

SELECT ID, Desc FROM TableName
UNION ALL
SELECT DISTINCT NULL AS NullID, "ALL" AS NullDesc
FROM TableName;


Then the query criteria clause that I gave above also will work.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So my second question of the day. For a combo box, how does one
select
all?
I've seen it somewhere using a * but can't find it and am in a bit
of a
time
crunch. Thanks! Jani
Click to expand...
Click to expand...
Click to expand...
 
Ken - Yesterday I had a message ready to go... didn't get it sent so am
redoing and hoping you're checking on me today! We really need to be able to
either have 'all' or an '*' indicating all and then by individual associates.
Because of the number of people using the database, we really need it this
way. Below is the sql for the report record source row. I've changed the
combo box to run off a table and inserted an '*' (macro deletes and appends
the data - this works fine). I so much appreciate your taking your time to
help with this. Thanks!!! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.Responsibility,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM dbo_uCMMSNonPMTasks
WHERE
(((dbo_uCMMSNonPMTasks.Responsibility)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.Responsibility, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


Ken Snell said:
Do not select anything in the combo box -- leave it "empty" (which means the
combo box has a NULL value in it). Then run the query. All associates will
be returned who have info in the date range selected.

If you really want to have an option in the dropdown list that can be
selected for "ALL", post the specific SQL query statement that you're using
for the combobox itself and we can modify it (and the report query) to do
that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jani said:
Thanks, Ken... I hope you come back to this issue though. I have changed
the
query as shown below. The drop-down box displays only the data for the
associates that have records between the dates, and the report has the
correct records for dates chosen. However, I still don't see a way to
select
all associates which have entered maintenance issues for dates selected.
Manager really doesn't want to run off ten reports if wanting to see all
closed issues. Need some additional help if possible. Thanks again! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


Ken Snell said:
Change the WHERE clause of the report's record source query to this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets of
criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So you 3 great guys all say I didn't provide enough information so I
will
now
try and give better, more thorough information (and you are so right
when
I
relook at my problem!). I have a combo box based on a query that pulls
employees from a table based on a beginning and end date. A query is
used
in
a report which displays information about a maintenance issue. I use a
query
to reduce the number of employees in the combo box list so one doesn't
pick
employee after employee that did not do any maintenance work for the
period
of time chosen. What I want to do is have the ability in the report to
display records for all employees if one chooses to do so rather than
just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION, dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location])
AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND ((dbo_uCMMSNonPMTasks.[COMP
DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report :
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null));

Thanks!!!!


:

Select all in a combo box? I assume you don't mean selecting all the
items
in the dropdown list, because that cannot be done with a combo box.

If you want the ability to select an item in the dropdown list that
will
*mean* you want a query using the combo box as a criterion to return
all
records, the easiest way is to leave the combo box empty and change
your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or
Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to mean
"all",
you
would need to change your combobox's RowSource query to a UNION query
that
includes a record that means all. It'll be easier for us to assist
with
this
if you post the combobox's RowSource query. But let's assume that you
have
two values in that query: an ID field and a Desc field. The new query
would
be this:

SELECT ID, Desc FROM TableName
UNION ALL
SELECT DISTINCT NULL AS NullID, "ALL" AS NullDesc
FROM TableName;


Then the query criteria clause that I gave above also will work.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So my second question of the day. For a combo box, how does one
select
all?
I've seen it somewhere using a * but can't find it and am in a bit
of a
time
crunch. Thanks! Jani
Click to expand...
Click to expand...
Click to expand...
 
Jani -

I need the SQL statement that is in the combobox's RowSource property. That
is the query that we need to modify for allowing an "ALL" option in the
dropdown list. If the combobox's RowSource property contains the name of a
table or query, tell us that name.

Then we can suggest how to change your database so that it will do what you
seek.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jani said:
Ken - Yesterday I had a message ready to go... didn't get it sent so am
redoing and hoping you're checking on me today! We really need to be able
to
either have 'all' or an '*' indicating all and then by individual
associates.
Because of the number of people using the database, we really need it this
way. Below is the sql for the report record source row. I've changed the
combo box to run off a table and inserted an '*' (macro deletes and
appends
the data - this works fine). I so much appreciate your taking your time to
help with this. Thanks!!! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.Responsibility,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM dbo_uCMMSNonPMTasks
WHERE
(((dbo_uCMMSNonPMTasks.Responsibility)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.Responsibility, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


Ken Snell said:
Do not select anything in the combo box -- leave it "empty" (which means
the
combo box has a NULL value in it). Then run the query. All associates
will
be returned who have info in the date range selected.

If you really want to have an option in the dropdown list that can be
selected for "ALL", post the specific SQL query statement that you're
using
for the combobox itself and we can modify it (and the report query) to do
that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jani said:
Thanks, Ken... I hope you come back to this issue though. I have
changed
the
query as shown below. The drop-down box displays only the data for the
associates that have records between the dates, and the report has the
correct records for dates chosen. However, I still don't see a way to
select
all associates which have entered maintenance issues for dates
selected.
Manager really doesn't want to run off ten reports if wanting to see
all
closed issues. Need some additional help if possible. Thanks again!
Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Change the WHERE clause of the report's record source query to this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets of
criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So you 3 great guys all say I didn't provide enough information so I
will
now
try and give better, more thorough information (and you are so right
when
I
relook at my problem!). I have a combo box based on a query that
pulls
employees from a table based on a beginning and end date. A query is
used
in
a report which displays information about a maintenance issue. I use
a
query
to reduce the number of employees in the combo box list so one
doesn't
pick
employee after employee that did not do any maintenance work for the
period
of time chosen. What I want to do is have the ability in the report
to
display records for all employees if one chooses to do so rather
than
just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location])
AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND ((dbo_uCMMSNonPMTasks.[COMP
DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report :
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null));

Thanks!!!!


:

Select all in a combo box? I assume you don't mean selecting all
the
items
in the dropdown list, because that cannot be done with a combo box.

If you want the ability to select an item in the dropdown list that
will
*mean* you want a query using the combo box as a criterion to
return
all
records, the easiest way is to leave the combo box empty and change
your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or
Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to mean
"all",
you
would need to change your combobox's RowSource query to a UNION
query
that
includes a record that means all. It'll be easier for us to assist
with
this
if you post the combobox's RowSource query. But let's assume that
you
have
two values in that query: an ID field and a Desc field. The new
query
would
be this:

SELECT ID, Desc FROM TableName
UNION ALL
SELECT DISTINCT NULL AS NullID, "ALL" AS NullDesc
FROM TableName;


Then the query criteria clause that I gave above also will work.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So my second question of the day. For a combo box, how does one
select
all?
I've seen it somewhere using a * but can't find it and am in a
bit
of a
time
crunch. Thanks! Jani
Click to expand...
Click to expand...
Click to expand...
 
It's a table: tbl_ResponsibilityList.

Ken Snell said:
Jani -

I need the SQL statement that is in the combobox's RowSource property. That
is the query that we need to modify for allowing an "ALL" option in the
dropdown list. If the combobox's RowSource property contains the name of a
table or query, tell us that name.

Then we can suggest how to change your database so that it will do what you
seek.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jani said:
Ken - Yesterday I had a message ready to go... didn't get it sent so am
redoing and hoping you're checking on me today! We really need to be able
to
either have 'all' or an '*' indicating all and then by individual
associates.
Because of the number of people using the database, we really need it this
way. Below is the sql for the report record source row. I've changed the
combo box to run off a table and inserted an '*' (macro deletes and
appends
the data - this works fine). I so much appreciate your taking your time to
help with this. Thanks!!! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.Responsibility,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM dbo_uCMMSNonPMTasks
WHERE
(((dbo_uCMMSNonPMTasks.Responsibility)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.Responsibility, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


Ken Snell said:
Do not select anything in the combo box -- leave it "empty" (which means
the
combo box has a NULL value in it). Then run the query. All associates
will
be returned who have info in the date range selected.

If you really want to have an option in the dropdown list that can be
selected for "ALL", post the specific SQL query statement that you're
using
for the combobox itself and we can modify it (and the report query) to do
that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thanks, Ken... I hope you come back to this issue though. I have
changed
the
query as shown below. The drop-down box displays only the data for the
associates that have records between the dates, and the report has the
correct records for dates chosen. However, I still don't see a way to
select
all associates which have entered maintenance issues for dates
selected.
Manager really doesn't want to run off ten reports if wanting to see
all
closed issues. Need some additional help if possible. Thanks again!
Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Change the WHERE clause of the report's record source query to this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets of
criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So you 3 great guys all say I didn't provide enough information so I
will
now
try and give better, more thorough information (and you are so right
when
I
relook at my problem!). I have a combo box based on a query that
pulls
employees from a table based on a beginning and end date. A query is
used
in
a report which displays information about a maintenance issue. I use
a
query
to reduce the number of employees in the combo box list so one
doesn't
pick
employee after employee that did not do any maintenance work for the
period
of time chosen. What I want to do is have the ability in the report
to
display records for all employees if one chooses to do so rather
than
just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location])
AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND ((dbo_uCMMSNonPMTasks.[COMP
DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report :
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null));

Thanks!!!!


:

Select all in a combo box? I assume you don't mean selecting all
the
items
in the dropdown list, because that cannot be done with a combo box.

If you want the ability to select an item in the dropdown list that
will
*mean* you want a query using the combo box as a criterion to
return
all
records, the easiest way is to leave the combo box empty and change
your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or
Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to mean
"all",
you
would need to change your combobox's RowSource query to a UNION
query
that
includes a record that means all. It'll be easier for us to assist
with
this
if you post the combobox's RowSource query. But let's assume that
you
have
two values in that query: an ID field and a Desc field. The new
query
would
be this:

SELECT ID, Desc FROM TableName
UNION ALL
SELECT DISTINCT NULL AS NullID, "ALL" AS NullDesc
FROM TableName;


Then the query criteria clause that I gave above also will work.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So my second question of the day. For a combo box, how does one
select
all?
I've seen it somewhere using a * but can't find it and am in a
bit
of a
time
crunch. Thanks! Jani
Click to expand...
Click to expand...
Click to expand...
 
OK. Sorry, need just a bit more info (I neglected to request it in my last
post) about the combo box. Please post the values for these properties of
the combo box:

Column Count
Column Widths
Bound Column

Also, please identify the data types of the fields in the
tbl_ResponsibilityList table, in order of the fields in the table.

And, one clarifying question: in one of your very early post replies, you
indicated that this query populates the combo box:

SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Is this for a different combo box, or the same combo box? In other words, is
the above query the one that is used for the combo box that needs the "all"
option? Sorry for my confusion.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Jani said:
It's a table: tbl_ResponsibilityList.

Ken Snell said:
Jani -

I need the SQL statement that is in the combobox's RowSource property.
That
is the query that we need to modify for allowing an "ALL" option in the
dropdown list. If the combobox's RowSource property contains the name of
a
table or query, tell us that name.

Then we can suggest how to change your database so that it will do what
you
seek.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jani said:
Ken - Yesterday I had a message ready to go... didn't get it sent so am
redoing and hoping you're checking on me today! We really need to be
able
to
either have 'all' or an '*' indicating all and then by individual
associates.
Because of the number of people using the database, we really need it
this
way. Below is the sql for the report record source row. I've changed
the
combo box to run off a table and inserted an '*' (macro deletes and
appends
the data - this works fine). I so much appreciate your taking your time
to
help with this. Thanks!!! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.Responsibility,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM dbo_uCMMSNonPMTasks
WHERE
(((dbo_uCMMSNonPMTasks.Responsibility)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.Responsibility, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Do not select anything in the combo box -- leave it "empty" (which
means
the
combo box has a NULL value in it). Then run the query. All associates
will
be returned who have info in the date range selected.

If you really want to have an option in the dropdown list that can be
selected for "ALL", post the specific SQL query statement that you're
using
for the combobox itself and we can modify it (and the report query) to
do
that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thanks, Ken... I hope you come back to this issue though. I have
changed
the
query as shown below. The drop-down box displays only the data for
the
associates that have records between the dates, and the report has
the
correct records for dates chosen. However, I still don't see a way
to
select
all associates which have entered maintenance issues for dates
selected.
Manager really doesn't want to run off ten reports if wanting to see
all
closed issues. Need some additional help if possible. Thanks again!
Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Change the WHERE clause of the report's record source query to
this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets of
criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So you 3 great guys all say I didn't provide enough information
so I
will
now
try and give better, more thorough information (and you are so
right
when
I
relook at my problem!). I have a combo box based on a query that
pulls
employees from a table based on a beginning and end date. A query
is
used
in
a report which displays information about a maintenance issue. I
use
a
query
to reduce the number of employees in the combo box list so one
doesn't
pick
employee after employee that did not do any maintenance work for
the
period
of time chosen. What I want to do is have the ability in the
report
to
display records for all employees if one chooses to do so rather
than
just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location])
AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND
((dbo_uCMMSNonPMTasks.[COMP
DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report :
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null));

Thanks!!!!


:

Select all in a combo box? I assume you don't mean selecting all
the
items
in the dropdown list, because that cannot be done with a combo
box.

If you want the ability to select an item in the dropdown list
that
will
*mean* you want a query using the combo box as a criterion to
return
all
records, the easiest way is to leave the combo box empty and
change
your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or
Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to
mean
"all",
you
would need to change your combobox's RowSource query to a UNION
query
that
includes a record that means all. It'll be easier for us to
assist
with
this
if you post the combobox's RowSource query. But let's assume
that
you
have
two values in that query: an ID field and a Desc field. The new
query
would
be this:

SELECT ID, Desc FROM TableName
UNION ALL
SELECT DISTINCT NULL AS NullID, "ALL" AS NullDesc
FROM TableName;


Then the query criteria clause that I gave above also will work.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So my second question of the day. For a combo box, how does
one
select
all?
I've seen it somewhere using a * but can't find it and am in a
bit
of a
time
crunch. Thanks! Jani
Click to expand...
Click to expand...
Click to expand...
 
Geez - don't be sorry!
Column Count: 1
Column Width: 1"
Bound Column: 1

The tbl_ResponsibilityList is created from a macro and is only one text
field, field size 50. From a query I pull those 'responsible' and append to a
table after resetting it, and then append an *. Thought I could figure out
how to use the * as the 'all.'

On your clarifying quesion, yes that query did populate the box correctly
but then I couldn't get it to work for all so gave up and went with the macro
so I could get an * in... which hasn't worked for me either.

You are so patient with me... thanks!
Ken Snell said:
OK. Sorry, need just a bit more info (I neglected to request it in my last
post) about the combo box. Please post the values for these properties of
the combo box:

Column Count
Column Widths
Bound Column

Also, please identify the data types of the fields in the
tbl_ResponsibilityList table, in order of the fields in the table.

And, one clarifying question: in one of your very early post replies, you
indicated that this query populates the combo box:

SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Is this for a different combo box, or the same combo box? In other words, is
the above query the one that is used for the combo box that needs the "all"
option? Sorry for my confusion.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Jani said:
It's a table: tbl_ResponsibilityList.

Ken Snell said:
Jani -

I need the SQL statement that is in the combobox's RowSource property.
That
is the query that we need to modify for allowing an "ALL" option in the
dropdown list. If the combobox's RowSource property contains the name of
a
table or query, tell us that name.

Then we can suggest how to change your database so that it will do what
you
seek.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ken - Yesterday I had a message ready to go... didn't get it sent so am
redoing and hoping you're checking on me today! We really need to be
able
to
either have 'all' or an '*' indicating all and then by individual
associates.
Because of the number of people using the database, we really need it
this
way. Below is the sql for the report record source row. I've changed
the
combo box to run off a table and inserted an '*' (macro deletes and
appends
the data - this works fine). I so much appreciate your taking your time
to
help with this. Thanks!!! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.Responsibility,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM dbo_uCMMSNonPMTasks
WHERE
(((dbo_uCMMSNonPMTasks.Responsibility)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND ((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is Null))
ORDER BY dbo_uCMMSNonPMTasks.Responsibility, dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Do not select anything in the combo box -- leave it "empty" (which
means
the
combo box has a NULL value in it). Then run the query. All associates
will
be returned who have info in the date range selected.

If you really want to have an option in the dropdown list that can be
selected for "ALL", post the specific SQL query statement that you're
using
for the combobox itself and we can modify it (and the report query) to
do
that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thanks, Ken... I hope you come back to this issue though. I have
changed
the
query as shown below. The drop-down box displays only the data for
the
associates that have records between the dates, and the report has
the
correct records for dates chosen. However, I still don't see a way
to
select
all associates which have entered maintenance issues for dates
selected.
Manager really doesn't want to run off ten reports if wanting to see
all
closed issues. Need some additional help if possible. Thanks again!
Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Change the WHERE clause of the report's record source query to
this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets of
criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So you 3 great guys all say I didn't provide enough information
so I
will
now
try and give better, more thorough information (and you are so
right
when
I
relook at my problem!). I have a combo box based on a query that
pulls
employees from a table based on a beginning and end date. A query
is
used
in
a report which displays information about a maintenance issue. I
use
a
query
to reduce the number of employees in the combo box list so one
doesn't
pick
employee after employee that did not do any maintenance work for
the
period
of time chosen. What I want to do is have the ability in the
report
to
display records for all employees if one chooses to do so rather
than
just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location])
AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND
((dbo_uCMMSNonPMTasks.[COMP
DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report :
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null));

Thanks!!!!


:

Select all in a combo box? I assume you don't mean selecting all
the
items
in the dropdown list, because that cannot be done with a combo
box.

If you want the ability to select an item in the dropdown list
that
will
*mean* you want a query using the combo box as a criterion to
return
all
records, the easiest way is to leave the combo box empty and
change
your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or
Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to
mean
Click to expand...
Click to expand...
Click to expand...
 
OK, assuming that the following query is the one that provides the data to
the combobox:

SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;


Modify the above query to this (assuming that the above SQL statement is the
value of the RowSource property for the combobox):

SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY, 1 AS SortOrder
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
UNION ALL
SELECT DISTINCT " < all >" AS FakeRESPONSIBILITY, 0 AS SortOrder
FROM qry_ResponsibleForClosedNonPMReport
ORDER BY SortOrder, qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;


Change your combobox's properties to this:
Column Count: 2
Column Width: 1"; 0"


Then modify the WHERE clause of the report's record source query to this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN ?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN ?]="N"
AND [Forms]![frm_CMMSMainForm]![cboResponsible] = " < all >");:


If you want to use something other than " < all >" as the "All" selection,
be sure to change it in both the combobox's RowSource query and in the
report's RecordSource query.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Jani said:
Geez - don't be sorry!
Column Count: 1
Column Width: 1"
Bound Column: 1

The tbl_ResponsibilityList is created from a macro and is only one text
field, field size 50. From a query I pull those 'responsible' and append
to a
table after resetting it, and then append an *. Thought I could figure out
how to use the * as the 'all.'

On your clarifying quesion, yes that query did populate the box correctly
but then I couldn't get it to work for all so gave up and went with the
macro
so I could get an * in... which hasn't worked for me either.

You are so patient with me... thanks!
Ken Snell said:
OK. Sorry, need just a bit more info (I neglected to request it in my
last
post) about the combo box. Please post the values for these properties of
the combo box:

Column Count
Column Widths
Bound Column

Also, please identify the data types of the fields in the
tbl_ResponsibilityList table, in order of the fields in the table.

And, one clarifying question: in one of your very early post replies, you
indicated that this query populates the combo box:

SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Is this for a different combo box, or the same combo box? In other words,
is
the above query the one that is used for the combo box that needs the
"all"
option? Sorry for my confusion.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Jani said:
It's a table: tbl_ResponsibilityList.

:

Jani -

I need the SQL statement that is in the combobox's RowSource property.
That
is the query that we need to modify for allowing an "ALL" option in
the
dropdown list. If the combobox's RowSource property contains the name
of
a
table or query, tell us that name.

Then we can suggest how to change your database so that it will do
what
you
seek.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ken - Yesterday I had a message ready to go... didn't get it sent so
am
redoing and hoping you're checking on me today! We really need to be
able
to
either have 'all' or an '*' indicating all and then by individual
associates.
Because of the number of people using the database, we really need
it
this
way. Below is the sql for the report record source row. I've changed
the
combo box to run off a table and inserted an '*' (macro deletes and
appends
the data - this works fine). I so much appreciate your taking your
time
to
help with this. Thanks!!! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.Responsibility,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM dbo_uCMMSNonPMTasks
WHERE
(((dbo_uCMMSNonPMTasks.Responsibility)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null))
ORDER BY dbo_uCMMSNonPMTasks.Responsibility,
dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Do not select anything in the combo box -- leave it "empty" (which
means
the
combo box has a NULL value in it). Then run the query. All
associates
will
be returned who have info in the date range selected.

If you really want to have an option in the dropdown list that can
be
selected for "ALL", post the specific SQL query statement that
you're
using
for the combobox itself and we can modify it (and the report query)
to
do
that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thanks, Ken... I hope you come back to this issue though. I have
changed
the
query as shown below. The drop-down box displays only the data
for
the
associates that have records between the dates, and the report
has
the
correct records for dates chosen. However, I still don't see a
way
to
select
all associates which have entered maintenance issues for dates
selected.
Manager really doesn't want to run off ten reports if wanting to
see
all
closed issues. Need some additional help if possible. Thanks
again!
Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Change the WHERE clause of the report's record source query to
this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND
dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND
dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets
of
criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So you 3 great guys all say I didn't provide enough
information
so I
will
now
try and give better, more thorough information (and you are so
right
when
I
relook at my problem!). I have a combo box based on a query
that
pulls
employees from a table based on a beginning and end date. A
query
is
used
in
a report which displays information about a maintenance issue.
I
use
a
query
to reduce the number of employees in the combo box list so one
doesn't
pick
employee after employee that did not do any maintenance work
for
the
period
of time chosen. What I want to do is have the ability in the
report
to
display records for all employees if one chooses to do so
rather
than
just
one employee. Here's the two queries for the combo box list:

Code for 1st query:
SELECT dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY
FROM dbo_uCMMSNonPMTasks
GROUP BY dbo_uCMMSNonPMTasks.LOCATION,
dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[OPEN ?], dbo_uCMMSNonPMTasks.[COMP DATE]
HAVING
(((dbo_uCMMSNonPMTasks.LOCATION)=[Forms]![frm_CMMSMainForm]![Location])
AND
((dbo_uCMMSNonPMTasks.[OPEN ?])="N") AND
((dbo_uCMMSNonPMTasks.[COMP
DATE])
Between [Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY;

Code for 2nd query that populates the combo box:
SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Here's the code query code for 'record source' for the report
:
SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED],
dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT
HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null));

Thanks!!!!


:

Select all in a combo box? I assume you don't mean selecting
all
the
items
in the dropdown list, because that cannot be done with a
combo
box.

If you want the ability to select an item in the dropdown
list
that
will
*mean* you want a query using the combo box as a criterion to
return
all
records, the easiest way is to leave the combo box empty and
change
your
query criterion to this:

WHERE FieldName = Forms!FormName!ComboBoxName Or
Forms!FormName!ComboBoxName
Is Null

If you want to physically select an item in the combo box to
mean
Click to expand...
Click to expand...
Click to expand...
 
Ken. It's working perfectly! Thank you so very, very much. You are a very
patient guy and I appreciate the help that you and others give to us novices.
Saying Thanks just doesn't seem like enough! Don't know what I'd do without
this site. Jani

Ken Snell said:
OK, assuming that the following query is the one that provides the data to
the combobox:

SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;


Modify the above query to this (assuming that the above SQL statement is the
value of the RowSource property for the combobox):

SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY, 1 AS SortOrder
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
UNION ALL
SELECT DISTINCT " < all >" AS FakeRESPONSIBILITY, 0 AS SortOrder
FROM qry_ResponsibleForClosedNonPMReport
ORDER BY SortOrder, qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;


Change your combobox's properties to this:
Column Count: 2
Column Width: 1"; 0"


Then modify the WHERE clause of the report's record source query to this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN ?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND dbo_uCMMSNonPMTasks.[OPEN ?]="N"
AND [Forms]![frm_CMMSMainForm]![cboResponsible] = " < all >");:


If you want to use something other than " < all >" as the "All" selection,
be sure to change it in both the combobox's RowSource query and in the
report's RecordSource query.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Jani said:
Geez - don't be sorry!
Column Count: 1
Column Width: 1"
Bound Column: 1

The tbl_ResponsibilityList is created from a macro and is only one text
field, field size 50. From a query I pull those 'responsible' and append
to a
table after resetting it, and then append an *. Thought I could figure out
how to use the * as the 'all.'

On your clarifying quesion, yes that query did populate the box correctly
but then I couldn't get it to work for all so gave up and went with the
macro
so I could get an * in... which hasn't worked for me either.

You are so patient with me... thanks!
Ken Snell said:
OK. Sorry, need just a bit more info (I neglected to request it in my
last
post) about the combo box. Please post the values for these properties of
the combo box:

Column Count
Column Widths
Bound Column

Also, please identify the data types of the fields in the
tbl_ResponsibilityList table, in order of the fields in the table.

And, one clarifying question: in one of your very early post replies, you
indicated that this query populates the combo box:

SELECT qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
FROM qry_ResponsibleForClosedNonPMReport
GROUP BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY
ORDER BY qry_ResponsibleForClosedNonPMReport.RESPONSIBILITY;

Is this for a different combo box, or the same combo box? In other words,
is
the above query the one that is used for the combo box that needs the
"all"
option? Sorry for my confusion.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



It's a table: tbl_ResponsibilityList.

:

Jani -

I need the SQL statement that is in the combobox's RowSource property.
That
is the query that we need to modify for allowing an "ALL" option in
the
dropdown list. If the combobox's RowSource property contains the name
of
a
table or query, tell us that name.

Then we can suggest how to change your database so that it will do
what
you
seek.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Ken - Yesterday I had a message ready to go... didn't get it sent so
am
redoing and hoping you're checking on me today! We really need to be
able
to
either have 'all' or an '*' indicating all and then by individual
associates.
Because of the number of people using the database, we really need
it
this
way. Below is the sql for the report record source row. I've changed
the
combo box to run off a table and inserted an '*' (macro deletes and
appends
the data - this works fine). I so much appreciate your taking your
time
to
help with this. Thanks!!! Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.Responsibility,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM dbo_uCMMSNonPMTasks
WHERE
(((dbo_uCMMSNonPMTasks.Responsibility)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null))
ORDER BY dbo_uCMMSNonPMTasks.Responsibility,
dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Do not select anything in the combo box -- leave it "empty" (which
means
the
combo box has a NULL value in it). Then run the query. All
associates
will
be returned who have info in the date range selected.

If you really want to have an option in the dropdown list that can
be
selected for "ALL", post the specific SQL query statement that
you're
using
for the combobox itself and we can modify it (and the report query)
to
do
that.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thanks, Ken... I hope you come back to this issue though. I have
changed
the
query as shown below. The drop-down box displays only the data
for
the
associates that have records between the dates, and the report
has
the
correct records for dates chosen. However, I still don't see a
way
to
select
all associates which have entered maintenance issues for dates
selected.
Manager really doesn't want to run off ten reports if wanting to
see
all
closed issues. Need some additional help if possible. Thanks
again!
Jani

SELECT DISTINCTROW dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE SUBMITTED], dbo_uCMMSNonPMTasks.[COMP
DATE],
dbo_uCMMSNonPMTasks.[ID #], dbo_uCMMSNonPMTasks.TASK,
dbo_uCMMSNonPMTasks.[EST HRS], dbo_uCMMSNonPMTasks.[ACT HOURS],
dbo_uCMMSNonPMTasks.[OPEN ?]
FROM [EQUIP LIST] INNER JOIN dbo_uCMMSNonPMTasks ON [EQUIP
LIST].EQUIPMENT
=
dbo_uCMMSNonPMTasks.EQUIPMENT
WHERE
(((dbo_uCMMSNonPMTasks.RESPONSIBILITY)=[Forms]![frm_CMMSMainForm]![cboResponsible])
AND ((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N")) OR (((dbo_uCMMSNonPMTasks.[COMP DATE]) Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate]) AND
((dbo_uCMMSNonPMTasks.[OPEN
?])="N") AND (([Forms]![frm_CMMSMainForm]![cboResponsible]) Is
Null))
ORDER BY dbo_uCMMSNonPMTasks.RESPONSIBILITY,
dbo_uCMMSNonPMTasks.[DATE
SUBMITTED] DESC , dbo_uCMMSNonPMTasks.[COMP DATE] DESC;


:

Change the WHERE clause of the report's record source query to
this:

WHERE
(dbo_uCMMSNonPMTasks.RESPONSIBILITY=[Forms]![frm_CMMSMainForm]![cboResponsible]
AND dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND
dbo_uCMMSNonPMTasks.[OPEN
?]="N")
OR
(dbo_uCMMSNonPMTasks.[COMP DATE] Between
[Forms]![frm_CMMSMainForm]![StartDate] And
[Forms]![frm_CMMSMainForm]![EndDate] AND
dbo_uCMMSNonPMTasks.[OPEN
?]="N" AND [Forms]![frm_CMMSMainForm]![cboResponsible] Is Null);


You need to have the OR logic operator be between the two sets
of
criteria.
In your query, it's part of the logic for all the criteria.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



So you 3 great guys all say I didn't provide enough
information
so I
will
now
try and give better, more thorough information (and you are so
right
Click to expand...
Click to expand...
Click to expand...
 
Jani said:
Ken. It's working perfectly! Thank you so very, very much. You are a very
patient guy and I appreciate the help that you and others give to us
novices.
Saying Thanks just doesn't seem like enough! Don't know what I'd do
without
this site. Jani
Click to expand...

You're welcome, and our pleasure to assist you with making your database
work the way YOU want it to! Good luck.
 
Back
Top