Access 2003: Using Combobox results with ALL for Query Criteria

I'm trying to use the attached microsoft "trick" to include ALL as an option in my combobox.  If the user clicks ALL, then the query should ignore that criteria.

Any ideas to accomplish this?

Thanks,
Create a new Query in Design View.
Close the Add Table dialog box.
From the Query menu, select SQL Specific, and then click Union.
Add the following SQL statement to the Query:
SELECT LastName & ", " & FirstName, EmployeeID FROM Employees UNION SELECT '(All)', null FROM Employees
					

Save the Query as EmpUnionAll, and then close the Query.
Open the Employees form in Design view.
Double-click on the ReportsTo combo box to bring up the control properties.
On the Data tab of the Properties box, change the Row Source to the EmpUnionAll query that you created above.
Change the Bound Column property on the Data tab from 1 to 2.
On the Format tab, change the column width from 0 to 2;0.
Open the form in Form view.

Notice that you can now select '(All)' from the combo box list.

Open in new window

OnsiteSupportAsked:
Who is Participating?
 
Dale FyeCommented:
Generally, I prefer to put the Null and EmployeeID as the first column and keep the bound column set to 1 and the column widths set to 0, 2.

Then, your query could read something like:

SELECT Fields from yourTable
WHERE Form!yourForm!cboName IS NULL OR [EmployeeID] = NZ(Form!yourForm!cboName, 0)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" If the user clicks ALL, then the query should ignore that criteria."

What query ?

mx
0
 
OnsiteSupportAuthor Commented:
fyed, I'm not following the WHERE clause.  If the ComboBox returned something other than NULL what is being passed to the criteria?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OnsiteSupport:

Try this ... changing to your actual object names:


SELECT Table1.*
FROM Table1
WHERE (((Table1.FIELD1)=IIf([Forms]![YourFormName]![YourComboBoxName]="(ALL)",[FIELD1],[Forms]![YourFormName]![YourComboBoxName])));

mx

0
 
Dale FyeCommented:
The bound column (EmployeeID) of the "All" record is NULL, so the value of Form!yourForm!cboName would be NULL if "All" gets selected.

The criteria, when evaluated would read:

WHERE NULL IS NULL OR [EmployeeID] = 0

Since NULL IS NULL would always be TRUE the criteria becomes

WHERE TRUE

Which should return all the records from the SELECT clause.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I don't see it that way.  Null is not the same as "ALL"  Null is No selection at all (no pun).   ALL is everything.  

mx
0
 
Dale FyeCommented:
MX,

If you look at his union query, he set the EmployeeID of the "ALL" record to NULL.  Therefore, the value of the listbox will be NULL, if the user selects "ALL"

Dale
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ALL examples:

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;

==

SELECT EmpName, 2 FROM tblEmp GROUP BY EmpName
UNION SELECT "AUTO", 1 FROM tblEMP
ORDER BY 2;

mx
0
 
OnsiteSupportAuthor Commented:
Thank you guys....Think i'm getting this...on to the next posting!! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.