Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

MS Access Union Query to fill text box with "All" then ID's

I need to fill a list box dynamically with ID's and the word All.

I would like to use a union statment like the following....

Select "All" as id
Union
Select ID from Issues order by ID.

But this fails, how do I do this in MS Access (2003).  

Thanks,
0
Rog D
Asked:
Rog D
  • 2
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Rog,
Take a look at the following MSKB article:
http://support.microsoft.com/kb/210290
  The second solution is your UNION solution.  Critical thing there is that the number of output fields in all the SELECTS must be the same:
 Also look at the fist solution.  You might be interested in that as an alternative.
JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
and your statement would be:
Select "All" as id from Issues
Union
Select ID from Issues order by ID
  but you probably need another couple of fields in there for your combo.  Typically when one uses a combo, you display one field for the user and ("all"), and then store something else in the control itself (i.e. NULL)
JimD.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Example:

SELECT ID, 5 FROM tblEmp GROUP BY EmpName
UNION SELECT "<Select All>", 2 FROM tblEMP
ORDER BY 2;

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Another example with more options:


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;

mx
0
 
Rog DManager Inforamtion SystemsAuthor Commented:
All answers were very good, but this was the first answer I just didn't have time to follow up until today.

Thanks.

Roger
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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