Data Management

Use SQL to create a more friendly format for list items

While it's versatile, the list box control for Microsoft Access doesn't have the formatting that users are accustomed to seeing. Here are two ways to create more familiar looking list items in Access.

Microsoft Access’s list box control is flexible enough to display multiple fields of data for each list item. Unfortunately, lists sometimes lack the familiar formatting users are used to seeing. For example, “names” generally appears in one of two familiar formats: “firstname, lastname” or “lastname, firstname.” A list box, without help, can’t handle that kind of format.

I’ll show you two easy ways to create more meaningful and familiar-looking list items. Fortunately, neither technique degrades performance, regardless of the environment. (The techniques discussed in this article work with both list box and combo box controls.)

Creating a simple list box control
Figure A shows a typical, and unformatted, list box control with three columns of data: the record’s primary key and two data fields. Let's begin by creating this list box. Later, we’ll apply our techniques to improve the way the list looks.

Figure A
A SQL statement populates this control’s list.

We based the list on a simple table named tblNames comprising four fields: an AutoNumber field named NameID, two text fields named FirstName and LastName, and a Yes/No field named Status. Enter the names shown in Figure A and check Yes in the Status field for Moe Tell and Fran Tick. (We’ll use the Yes/No field later.) This list is adequate, but with a little effort you can improve its appearance—without changing the control’s functionality.

To create the control, open a new blank form, insert a list box control, and name the control lstNames. Then, enter the following SQL statement as the control’s Row Source property:
SELECT NameID, FirstName, LastName FROM tblNames

Enter 3 as the control’s Column Count property. I also set the following form properties, although they’re not critical to the control’s performance:
  • Scroll Bars—Neither
  • Record Selectors—No
  • Navigation Buttons—No
  • Dividing Lines—No

Manipulating the list items
Using SQL to populate a list control creates a very flexible control, at least where manipulating the actual list items is concerned. You can combine entries from more than one field into one item. Or, you can return specific labels or values depending on existing data. You can even use the two techniques together.

Concatenating items
Let’s begin by using Jet’s concatenation operator, &, to combine the first and last names into one column. (Use the + concatenation operator in an Access project if you use a stored procedure to populate the control.) With the form shown in Figure A in Design view, change the control’s Row Source property to
SELECT NameID, FirstName & " " & LastName AS Name FROM tblNames ORDER BY LastName

Since the statement combines two fields into one, the control accommodates one less field, so change the Column Count property from 3 to 2. Next, hide the primary key values by entering the value 0 as the Column Width property setting.

You might also reduce the width of the control, but doing so isn’t critical to the technique. The ORDER BY clause isn’t required, but it does sort the names by their last name entries. You can sort by any field in the data source. The modified control is shown in Figure B. Instead of returning the first and last names in separate columns, the statement concatenates the two entries into one, positioning the first name before the last name and separating the two with a space character.

Figure B
By concatenating the first and last name entries, you can format the names in a familiar manner.

Some users might prefer to see the names in “lastname, firstname” format. When that’s the case, simply use this statement:
SELECT NameID, LastName & ", " & FirstName AS Name FROM tblNames ORDER BY LastName

Similarly to the previous example, this statement concatenates the first and last names but positions the last name first and separates the two with a comma and a space character.

Evaluating items
Our first technique combines items, but sometimes the text or value you want to display isn’t stored as data. For example, tblNames contains a Yes/No field named Status. Let’s suppose that value indicates each person’s employment status. Including the Status field would display only Yes or No and could be meaningless to the user. More descriptive values such as Retired and Active would be more helpful to the user than Yes and No.

You can use an IIf() function to return the appropriate status description for each member. In Design view, enter the following SQL statement as the control’s Row Source property:
SELECT NameID, FirstName & " " & LastName AS Name, IIf([Status]=True,"Retired","Active") AS [Employee Status] FROM tblNames ORDER BY LastName

Change the Column Count property from 2 to 3.

The IIf() function checks each record’s Status entry, which will be Yes or No, or True or False, respectively. When the field equals Yes, the IIf() function returns the string “Retired.” When Status equals No False, the function returns “Active.” The modified control is shown in Figure C. As you can see, each member is identified as either Retired or Active, which is much more meaningful to the user than a simple Yes or No.

Figure C
An IIf() function evaluates a Yes/No field and returns Retired or Active, accordingly.

List controls are a great way to display a number of items, but they’re limited because you can’t format or otherwise control the way those items appear in the list. Using SQL’s concatenation operator to combine entries or a simple IIf() function to evaluate values can offer a bit of nontraditional formatting to make those items appear in a more familiar and meaningful way.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox