Question Locked

Using a combo box as criteria in Access

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In a Microsoft Access 2003 database I have a table of information, it happens to be a list of rooms in a building and various information about each room. One column is called NAME and i have a combo box that a user can pull down and select from the various room names in the building (lots of duplicates, CLASSROOM for example). I have a button on the form which previews a report listing all the rooms that meet the criteria in the combo box

SELECT rooms.number, rooms.name
FROM rooms
WHERE (((rooms.name)=[forms]![roomquery].[combo1]))
ORDER BY rooms.number;

what i can't figure out is how to get it to list ALL of the rooms when someone does not select something from the combo box. the combo box gets it's row source from this query

SELECT rooms.name
FROM rooms
GROUP BY rooms.name
ORDER BY rooms.name;

so that it only gives the user the option of picking valid room names. i figured out how to do it with VB code attached to the button, but i end up with a nasty if-then-else tree since there will be about 20 fields on this form when it's complete. any suggestions would be greatly appreciated!

2 replies

by russhyy

Try a UNION query like:

SELECT rooms.name
FROM rooms
UNION ' ALL'
FROM rooms
GROUP BY rooms.name
ORDER BY rooms.name;

google for exact syntax...

This discussion is closed to new replies.