INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using list boxes for Criteria

How to add 'ALL' to List Box or Combo Box. by DougStevens
Posted: 16 Aug 02

Quick answer is this:  Use a Union Query for the RowSource of the control.  Then use the LIKE operator with the criteria.

Example:
SETTING UP THE UNION QUERY
In the Northwind database there exists the Employees table.  Let's say we have the following SQL statement (query) for the RowSource to display the Employees in our control.  That control name is cbo1 within the form called Form1.
(*Note that the table has been Aliased to T1, the EmployeeID aliased to RecID, and there is a calculated expression concatenating the FirstName and LastName of the Employeed which is aliased as Employee.)

SELECT T1.EmployeeID AS RecID, [FirstName] & " " & [LastName] AS Employee
FROM Employees AS T1
ORDER BY [FirstName] & " " & [LastName];

Following is the result of that query:

RecID    Employee
2    Andrew Fuller
9    Anne Dodsworth
3    Janet Leverling
8    Laura Callahan
4    Margaret Peacock
6    Michael Suyama
1    Nancy Davolio
7    Robert King
5    Steven Buchanan



Now add another select query to UNION with the existing query (shown in bold).  Note that the world 'All' is in parenthesis.  This help it to be sorted to the top of the list.  Also, we're using the wild card "*" for the RecID.  Finally, the ORDER BY clause was changed to the alias name of Employee (the field to be sorted).  (Changes shown in green.)


SELECT "*" AS RecID, "(All)" AS Employee
FROM Employees AS T1

UNION


SELECT T1.EmployeeID AS RecID, [FirstName] & " " & [LastName] AS Employee
FROM Employees AS T1
ORDER BY Employee;


The above UNION query will produce the following results:

RecID    Employee
*    (All)
2    Andrew Fuller
9    Anne Dodsworth
3    Janet Leverling
8    Laura Callahan
4    Margaret Peacock
6    Michael Suyama
1    Nancy Davolio
7    Robert King
5    Steven Buchanan



SETTING UP THE CRITERIA
In the second query, where the combo box or list box is being used for as the criteria, we'll set up the criteria with the LIKE operator.  
Field:    [Employee ID]
Table:    [Employees]
Criteria: LIKE [Forms]![Form1]![cbo1]




SUMMARY
The UNION query in the control's RowSource property adds the wild card value and the word 'All' to the output list.

The second query's criteria will be based upon that given value (if it was a record id) or be based upon the wild card.  Because the wild card value is used, the LIKE operator was needed.





Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close