Query for All

[Jen]

Hi,

I have a table "Project" which fields are ID, Title, Owner, and details, and
due date.
I have a simple form which drop-down box of list of Owner.
Once an owner selected, macro open a query in below and display the result.

SELECT Project.ID, Project.Title, Project.Details, Project.Date
FROM Project
WHERE Project.Owner=Forms!SelectOwner!SelectOwner;

Now my manager asks me to add "All" to the drop-down box and once "All"
selected, display all project.

Could anybody help me?

Thank you in advance.

Jen

 

[KARL DEWEY]

Try this --
WHERE (Project.Owner=Forms!SelectOwner!SelectOwner) Or (Project.Owner Like
IIF(Forms!SelectOwner!SelectOwner = "All", "*", "");

 

[Douglas J. Steele]

Problem with that is that it will not return those rows where Project.Owner
is Null.

Better to use

WHERE ((Project.Owner=Forms!SelectOwner!SelectOwner)
OR (Forms!SelectOwner!SelectOwner = "All"))

 

[KenSheridan via AccessMonster.com]

Jen:

If you have an Owners table, to add "All" to the SelectOwner combo box set
its RowSource property to something like this:

SELECT Owner,1 as SortColumn
FROM Owners
UNION
SELECT "All",0
FROM Owners
ORDER BY SortColumn;

If you don't have a separate Owners table (though you really should to
control the integrity of the data) then just change Owners to Project in the
above SQL statement.

A UNION operation suppresses duplicates so you'll get one row with "All" plus
separate rows fro each owner. The SortColumn with the constants 0 and 1 as
its values puts the "All" row at the top of the list.

The change the query to:

SELECT Project.ID, Project.Title, Project.Details, Project.Date
FROM Project
WHERE Project.Owner=Forms!SelectOwner!SelectOwner
OR Forms!SelectOwner!SelectOwner = "All";

If the user selects "All" from the list then the query's WHERE clause will
evaluate to TRUE for every row, so all rows will be returned; if they select
an owner the it will evaluate to TRUE only for rows with he selected owner
value, so only those rows will be returned.

BTW I'd recommend avoiding Date as column name as its the name of a built in
function; ProjectDate would be better.

Ken Sheridan
Stafford, England

 

[Jen]

Thank you for your reply.

Jen

Problem with that is that it will not return those rows where Project.Owner
is Null.

Better to use

WHERE ((Project.Owner=Forms!SelectOwner!SelectOwner)
OR (Forms!SelectOwner!SelectOwner = "All"))

 

[Jen]

Thank you for your help.
It did work exactly I wanted.

Thanks,
Jen