How to Add a Catch-all Value to a Combo Box in Access

You know those multiple choice tests where the last option is, "All of the above"? Sometimes you want to provide your users that same functionality in an Access combo box.

There are two ways to provide this feature.

The Simple Way

The simplest way is to throw a little label under your combo box that says, "Leave blank for all."

I find I use this feature most often when I'm providing a form to let users run a report. Here's an example from a custom Accounts Payable program we wrote:

Keep It Simple, Stupid

The Slightly More Involved Way

Let's say you want your combo box to display a "[Show All]" option:

If you want to add an actual row to the combo box, you can easily do that with a UNION query.

SQL Server

In SQL Server, you can create a "table-less" union query:

-- The following SQL is invalid in MS Access, but runs in SQL Server
SELECT 0 AS AccountID, '[Show All]' AS AcctDesc
UNION ALL
SELECT Account.AccountID, Account.AcctDesc
FROM Account
ORDER BY AcctDesc;

Notice how there is no table in the first SELECT statement.

Notice how there is no table name included in the first SELECT statement above. In Access, you can write table-less SELECT queries and you can write UNION queries, but you can't write UNION queries that include table-less SELECT queries. (Sort of like how you can pick your friends and you can pick your nose, but you can't pick your friend's nose.)

MS Access (Jet/ACE)

If you try to use the same query in Access as we did in SQL Server above, you will get the following error:

The solution, then, is to ensure the first SELECT statement in the UNION query returns only a single row. Some people do this with a "TOP 1" or "DISTINCT" clause paired with a system table that is guaranteed to be in the database, like MSysObjects.

For the sake of performance and to avoid potential permissions issues when reading system tables, I prefer to use a custom table that's guaranteed to have only a single row:

Public Sub CreateDualTable()
    Dim strSql As String
    strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
    strSql = "INSERT INTO Dual (id) VALUES (1);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql

    strSql = "ALTER TABLE Dual" & vbNewLine & _
        vbTab & "ADD CONSTRAINT there_can_be_only_one" & vbNewLine & _
        vbTab & "CHECK (" & vbNewLine & _
        vbTab & vbTab & "(SELECT Count(*) FROM Dual) = 1" & vbNewLine & _
        vbTab & vbTab & ");"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
End Sub

Run the above code to create a table that is guaranteed to have one–and only one–record in it. With this table, we can make a simple adjustment to our SQL Server query to get it to run in Access:

SELECT 0 AS AccountID, "[Show All]" AS AcctDesc FROM Dual
UNION ALL 
SELECT Account.AccountID, Account.AcctDesc 
FROM Account 
ORDER BY AcctDesc;

Sorting the Catch-all Option

In the examples above, the catch-all option sorts to the top of the list because the square bracket comes before the letter "A" when sorted alphabetically.

You won't always be able to rely on that possibility, though. Luckily, we can simply add another literal field to our SELECT statements to enforce whatever sort order we want.

Let's re-write the above query to use an explicit sort order:

SELECT 0 AS AccountID, "[Show All]" AS AcctDesc, 1 AS SortOrder FROM Dual
UNION ALL 
SELECT Account.AccountID, Account.AcctDesc, 2 As SortOrder
FROM Account 
ORDER BY SortOrder DESC, AcctDesc;

Multiple Catch-all Values

What if we want to add another value? Just keep stacking UNION ALL statements:

SELECT -1 AS AccountID, "[Show None]" AS AcctDesc, 0 AS SortOrder FROM Dual
UNION ALL 
SELECT 0 AS AccountID, "[Show All]" AS AcctDesc, 1 AS SortOrder FROM Dual
UNION ALL
SELECT Account.AccountID, Account.AcctDesc, 2 As SortOrder
FROM Account 
ORDER BY SortOrder, AcctDesc;

Notice how the SortOrder column takes precedence over the AcctDesc for sorting purposes.

Why UNION ALL?

One final note about performance.

There are two ways to join SELECT statements in a UNION query: UNION or UNION ALL. The main difference is that UNION will check for duplicates among the SELECT statements and remove them automatically.

That's a handy feature if you need it, but it comes at a cost. The database engine has to do some amount of work to check for those duplicates.

Therefore, if you know your UNION query won't generate any duplicates, you can squeeze a bit of extra performance out of the database engine by telling it not to worry about checking for them. The UNION ALL does just that.

Will it make a noticeable difference? Almost certainly not. But it doesn't hurt, either.

External references

Table-less UNION query in MS Access (Jet/ACE)

This works as expected: SELECT “Mike” AS FName This fails with the error “Query input must contain at least one table or query”: SELECT “Mike” AS FNameUNION ALLSELECT “John” AS FName Is this ...

Stack Overflowmwolfe02

Referenced articles

Enforcing a Single-Row Table in MS Access and SQL Server

There are many uses for a table with one--and only one--row in it. Using such a table is simpler if you can rely on the sanctity of its one-row-ness.

No Longer SetMike Wolfe

Image by Adina Voicu from Pixabay