Data Management

How to get around a formatting problem in an Access list box control

Use Format() in SQL statements to display data when the native Access control doesn't produce the results you want.

I recently ran into a problem when I tried to add a Yes/No field to an existing list box control. The original developer used a SQL statement to populate the list box, which isn't right or wrong, it's just one of many ways to get the job done.Instead of displaying Yes and No, the list box displayed a column of -1's and 0's — the underlying values of Yes and No values, respectively. Using a fixed query would've fixed the problem, but I didn't want to change the existing solution that much. I wanted a SQL solution, not a fixed query.

Thanks to my colleague and sometimes co-author, I got a quick and easy SQL solution in the following form

SELECT Format(yesnofield,'Yes/No') FROM table

I found it so helpful, I thought I'd pass it along. Using Format() might never have occurred to me. Thanks Gustav!

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