[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

Access dropdown filter add choice for "all"

Hi!
I have an Access dropdown box that's based on a distinct list of all records in the query that is the datasource for that form.
I want that dropdown box to serve as a filter on the data that will show up in the form. (It's a continuous form.)
How do I do this?

Also, I'd like one of the choices to be "ALL", and to not filter the data, but to show all records.
How can I accomplish this?
0
etech0
Asked:
etech0
  • 6
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
For starters, here is how you get ALL - two examples:

SELECT DISTINCT CLng(EmpID), 2 FROM tblEmp
UNION SELECT "ALL", 1 FROM tblEMP
ORDER BY 2;


SELECT EmpName, 5 FROM tblEmp
UNION SELECT "<Add New>", 0 FROM tblEMP
UNION SELECT "-----------", 1 FROM tblEMP
UNION SELECT "<Select All>", 2 FROM tblEMP
UNION SELECT "-----------", 3 FROM tblEMP
ORDER BY 2;

Then, you need to handle the "ALL" case in the Combo box's AfterUpdate event ... to do what ever.

mx
0
 
etech0Author Commented:
I'm having a hard time understanding your code. Do I need to put both sections in?
Here is what I have so far:

SELECT DISTINCT CatWebWork2CopywritersQ.Username FROM CatWebWork2CopywritersQ ORDER BY CatWebWork2CopywritersQ.Username;

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
No ... the 2nd example was just an expanded example showing what 'can be done' - if you ever need more options.


SELECT DISTINCT CatWebWork2CopywritersQ.Username
FROM CatWebWork2CopywritersQ
UNION SELECT "<ALL>", "------------"
FROM CatWebWork2CopywritersQ
ORDER BY 2

Note: The orderby 2 means the 2nd column ... shortcut notation.

mx
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Past the SQL into a UNION Query - in the query designer window ... then run it ...


1

2
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The above is just to test the UNION query ... and confirm it shows the desired results ... 'ALL' s/b first in list.

mx
0
 
etech0Author Commented:
Hi!
I get an error:
"The number of columns in the two selected tables or queries of a union query do not match."
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
OOPS .... sorry, thought I saw two columns:


SELECT DISTINCT CatWebWork2CopywritersQ.Username
FROM CatWebWork2CopywritersQ
UNION SELECT "<ALL>"
FROM CatWebWork2CopywritersQ
ORDER BY 1
0
 
etech0Author Commented:
Great answer - thanks!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome ...

mx
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now