msgbartop
Just another WordPress site
msgbarbottom

09 Jun 09 Fake item in dropdown – MS Access SQL trick

I often use a combobox to filter.For an example, when browsing between the tasks assigned to the staff, it can be selected, which co-workers jobs should be displayed.It occurs sometimes, that in an Access form we have to include to a combobox / dropdown list an “Any/All” item, which can be used, when we don’t want to filter according to the co-workers.I have a stylish/interesting solution for this problem.I create a query, which shows only the active records in alphabetical order.It looks like this:allrecord Fake item in dropdown   MS Access SQL trickThe joker record is not yet included.I open the query for editing (View/SQL view).

SELECT tblUser.userID, tblUser.username
FROM tblUser
WHERE (((tblUser.active)=True))
ORDER BY tblUser.username;

I change the query source to:

SELECT tblUser.userID, tblUser.username
FROM tblUser
WHERE (((tblUser.active)=True))
UNION SELECT 0, 'All'
FROM tblUser
WHERE (((tblUser.userID)=1))

ORDER BY tblUser.username;

I base my virtual record on another record, but I assign its values myself.Therefore my query returns an enlarged list.When using on a form, I set the default value of the dropdown list to 0.I set the dropdown’s update event that when it is set to 0 the filter omits this dropdown, and in all other cases it omits the selected one.I hope you liked this hint.