To cater for Nulls use:
WHERE (Supervisor_Name = [Enter supervisor name:]
OR [Enter supervisor name:] IS NULL);
If the user enters nothing at the prompt the second part of the OR operation
will evaluate to TRUE for every row, so they'll all be returned whether the
supervisor name is Null or not.
However, names can be duplicated, even in a small workgroup. As I never
cease to bore people here by saying, I once worked in the same office with
two Maggie Taylors! Say you have a table Employees with a primary key column
EmployeeID. Each employee's supervisor would normally be identified by an
other column, e.g. SupervisorID which references the EmployeeID column of the
same table, e.g.
EmployeeID FirstName LastName SupervisorID
1 Sean Timmons
2 Ken Sheridan 1
i.e. you are my supervisor, but you as CEO have no supervisor.
Supervisors van be identified in this table by their EmployeeID value
existing in the SupervisorID column; this assumes you can't have a supervisor
with no employees to supervise! So you can identify them in a query by means
of a subquery using the EXISTS predicate (stick with me, this is leading
somewhere).
So, rather than using a crude system generated parameter prompt for the
supervisor name, a far better and more user friendly approach is to create a
simple dialogue form with a combo box which lists all supervisors. Set up
the combo box like this:
RowSource:
SELECT EmployeeID, FirstName & " " & LastName
FROM Employees AS E1
WHERE EXISTS
(SELECT *
FROM Employees AS E2
WHERE E2.SupervisorID = E1.EmployeeID)
ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
Add a button to the form which opens the query, or better still a form or
report based on the query. In the query reference the control on the form as
a parameter, again testing for NULL, so that if no supervisor is selected in
the combo box all rows are returned, e.g. to return a list of employees
earning less than 30,000 GBP p.a. and supervised by a selected supervisor, or
all earning less than 30,000 GBP if no supervisor is selected, a query would
be along these lines:
SELECT *
FROM Employees
WHERE (SupervisorID = Forms!YourForm!cboSupervisor
OR Forms!YourForm!cboSupervisor IS NULL)
AND Salary < 30000
ORDER BY Lastname, FirstName;
Notice that I've put the OR operation in parentheses here. These are
necessary to force it to evaluate independently of the AND operation. The
key thing about this approach, however, is that the supervisor is identified
by a unique numeric value, their EmployeeID, not by name even though the user
selects by name from the list. Of course, you'd have to add another column
to the combo box's RowSource, and adjust the ColumnCount and ColumnWidths
properties accordingly to distinguish somehow between supervisors of the same
name (you'd also set the ListWidth property to the sum of the ColumnWidths to
show both columns in the list). You'll probably say that we don’t have any
supervisors with the same names so it doesn't matter, but Murphy's Law states
that if something can go wrong it sooner or later will go wrong!
BTW if using a query as the RecordSource for a report omit the ORDER BY
clause. Use the report's internal sorting and grouping mechanism instead.
The above won't match you scenario precisely of course, and maybe not even
remotely, but the underlying principles apply to may situations where you
need to identify individual people (which is why we all have PINs).
Ken Sheridan
Stafford, England
Sean Timmons said:
D'oh! Thank you! I plead temporary insanity...
Wayne-I-M said:
Like "*" & [Supervisor name or press enter] & "*"
--
Wayne
Manchester, England.
Sean Timmons said:
I have a query that prompts for a criterion. In my case, Supervisor_Name.
What can I enter into the prompt to return all values - All values in
Supervisor_Name
