Need "Select All Option" in Combobox for Query Parameters (2 Viewers)

jimbo92

New member
Local time
Today, 08:58
Joined
Oct 26, 2019
Messages
10
I've seen this question posted elsewhere, but I can't make any of the answers work (I'm a beginner with Access).

I have a combobox that lets you select an employee, which gives the selected employee name as the parameter for a query. I want to have a "Select All" option in the combobox that tells the query to use all employees. This is my current Row Source code for the combobox:

SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name] FROM [Employee Directory] ORDER BY [Employee Name];

I've tried a couple different solutions from other threads--including adding UNION--but can't make it work. I also don't know if code needs to be added elsewhere as well. How do I make this work?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:58
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF! Yes, a UNION query is usually the correct approach for this. But if you're not sure how to implement it, then the simplest approach is to leave the control empty to represent a Select All choice.
 

Micron

AWF VIP
Local time
Today, 08:58
Joined
Oct 20, 2018
Messages
3,476
Saying I couldn't get anything to work doesn't help. You should post what you tried so as to give some insight as to the table(s) involved. Also, you can't do this with a query because passing "Select All" to it isn't going to give you all employees, is it? So is the problem how to get Select All in the list, or is it how to get the query to work if you choose that value?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:58
Joined
May 7, 2009
Messages
19,169
Code:
SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name] 
FROM [Employee Directory] 
WHERE [Employee Directory].[Employee Name] = 
IIF([FORMS]![yourForm]![yourCombo] = "Select All", [Employee Name], 
[FORMS]![yourForm]![yourCombo])
ORDER BY [Employee Name];
 

jimbo92

New member
Local time
Today, 08:58
Joined
Oct 26, 2019
Messages
10
Ok, so here's more info about what I've tried.

I can get an (All) option to appear in the ComboBox with this code in the Row Source for the ComboBox:

SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name] FROM [Employee Directory] ORDER BY [Employee Name] UNION SELECT Null as AllChoice , "(All)" as Bogus FROM [Employee Directory];

I can't make the query work though. I assume the query doesn't know what "(All)" means unless given more instructions. The code that arnelgp provided makes sense to me if I'm understanding it correctly, but where do I put that code?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:58
Joined
May 7, 2009
Messages
19,169
SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name]
FROM [Employee Directory]
WHERE [ID] = IIF(Trim([FORMS]!yourFormName!comboName & "")="", [ID], [FORMS]!yourFormName!comboName)
ORDER BY [Employee Name]
 

Micron

AWF VIP
Local time
Today, 08:58
Joined
Oct 20, 2018
Messages
3,476
he is off line at the moment so I'll assume it's OK to answer on his behalf. You said you had a query - he posted what your query ought to look like if you have the combo working. However, it doesn't look right to me. I interpret the IIF as "IF the combo value is "Select All" then use the employee name. IF not, use the combo value. There is no employee named Select All.

As I said, I don't think this can be done in a query because in the case of an actual name, you need a Where clause. When it's Select All, you can't use a Where clause. IMHO the query has to be built in code to use (or not) the Where clause.
EDIT - forgot to mention that if your combo bound field contains Null because of your Union query, then no employee has an ID value of Null. Untested, but I see that as being another block to using a query.
 

jimbo92

New member
Local time
Today, 08:58
Joined
Oct 26, 2019
Messages
10
Ok, been trying to adapt arnelgp's code and no luck yet. Here is the exact code I have currently for ComboBox Row Source:

SELECT [Employee Directory].[ID], [Employee Directory].[Employee Name] FROM [Employee Directory] ORDER BY [Employee Name] UNION SELECT Null as AllChoice , "(All)" as Bogus FROM [Employee Directory];

and the code for the Query:

SELECT [Employee Expenses].[Entry Number], [Employee Expenses].[Account Name], [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date], [Employee Expenses].[Hours Worked], [Employee Expenses].[Pay Per Hour], [Employee Expenses].[Amount Due], [Employee Expenses].Notes
FROM [Employee Expenses]
WHERE ((([Employee Expenses].[Employee Name])=[Forms]![Main Menu]![EmployeeEntered]) AND (([Employee Expenses].[Work Date]) Between [Forms]![Main Menu]![EmpStartDate] And [Forms]![Main Menu]![EmpEndDate])) OR ((([Forms]![Main Menu]![EmployeeEntered]) Is Null))
ORDER BY [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date];

With arnelgp's code for the query, it prompts me to enter a parameter value and the query doesn't work.
 

isladogs

MVP / VIP
Local time
Today, 12:58
Joined
Jan 14, 2017
Messages
18,186
Code:
Last post was moderated. Posting this to trigger email notifications.

In the combo after update event, I suggest you use something like this

Code:
If Me.comboname.Column(1) = "All" Then
  'use query with no WHERE filter so all records are selected
Else
  'use query with WHERE filter for value in combo
End If
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:58
Joined
May 7, 2009
Messages
19,169
what is the Combo name and the Bound Column number?
 

jimbo92

New member
Local time
Today, 08:58
Joined
Oct 26, 2019
Messages
10
Code:
Last post was moderated. Posting this to trigger email notifications.

In the combo after update event, I suggest you use something like this

Code:
If Me.comboname.Column(1) = "All" Then
  'use query with no WHERE filter so all records are selected
Else
  'use query with WHERE filter for value in combo
End If

I've gotten a slight variation of this to work! Thank you so much! I was trying to simplify it, but what I have on the form is a combobox that selects employee, then two date picker fields for start and end date. Then I press a button that generates a report. So I created two queries like you recommended I think--one for if "All" is selected and one for if a single employee is selected. Then I added an IF macro On Click for the button that opens the query/report.

It all seems to be working for now. Thanks everyone for being so helpful!
 

isladogs

MVP / VIP
Local time
Today, 12:58
Joined
Jan 14, 2017
Messages
18,186
Glad to hear you've got it working. We were all pleased to assist.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:58
Joined
May 7, 2009
Messages
19,169
can still be simplified:

SELECT [Employee Expenses].[Entry Number], [Employee Expenses].[Account Name], [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date], [Employee Expenses].[Hours Worked], [Employee Expenses].[Pay Per Hour], [Employee Expenses].[Amount Due], [Employee Expenses].Notes
FROM [Employee Expenses]
WHERE ((([Employee Expenses].[Employee Name])=IIF([Forms]![Main Menu]![EmployeeEntered]="(All)", [Employee Expenses].[Employee Name], [Forms]![Main Menu]![EmployeeEntered]) AND (([Employee Expenses].[Work Date]) Between [Forms]![Main Menu]![EmpStartDate] And [Forms]![Main Menu]![EmpEndDate]))
ORDER BY [Employee Expenses].[Employee Name], [Employee Expenses].[Work Date];
 

jimbo92

New member
Local time
Today, 08:58
Joined
Oct 26, 2019
Messages
10
Returns syntax error when I try to save query. Seems to be highlighting ORDER.
 

isladogs

MVP / VIP
Local time
Today, 12:58
Joined
Jan 14, 2017
Messages
18,186
I think there is a missing right bracket at the end of the previous line
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:58
Joined
Jul 9, 2003
Messages
16,244
'You Can simplify further by removing the Table Name (Only if there is a single Table)
'SELECT [Entry Number], [Account Name], [Employee Name], [Work Date], [Hours Worked], [Pay Per Hour], [Amount Due], Notes
'FROM [Employee Expenses]
'WHERE ((([Employee Name])=IIF([Forms]![Main Menu]![EmployeeEntered]="(All)", [Employee Name], [Forms]![Main Menu]![EmployeeEntered]) AND (([Work Date]) Between [Forms]![Main Menu]![EmpStartDate] And [Forms]![Main Menu]![EmpEndDate]))
'ORDER BY [Employee Name], [Work Date];

'And if you use Names WITHOUT SPACES you can get rid of the Square brackets too!
'SELECT EntryNumber, AccountName, EmployeeName, WorkDate, HoursWorked, PayPerHour, AmountDue, Notes
'FROM EmployeeExpenses
'WHERE (((EmployeeName)=IIF(Forms!MainMenu!EmployeeEntered="(All)", EmployeeName, Forms!MainMenu!EmployeeEntered) AND ((WorkDate) Between Forms!MainMenu!EmpStartDate And Forms!MainMenu!EmpEndDate))
'ORDER BY EmployeeName, WorkDate;
 

isladogs

MVP / VIP
Local time
Today, 12:58
Joined
Jan 14, 2017
Messages
18,186
And you can get rid of superfluous bracketing added by Access ;)
Code:
SELECT EntryNumber, AccountName, EmployeeName, WorkDate, HoursWorked, PayPerHour, AmountDue, Notes
FROM EmployeeExpenses
WHERE EmployeeName=IIf(Forms!MainMenu!EmployeeEntered="(All)", EmployeeName, Forms!MainMenu!EmployeeEntered)
 AND WorkDate Between Forms!MainMenu!EmpStartDate And Forms!MainMenu!EmpEndDate
ORDER BY EmployeeName, WorkDate
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 28, 2001
Messages
26,996
For future reference, since you have something working now: You had a situation where you would have put Null somewhere - but you can never match anything to Null because Null is never equal to anything INCLUDING another null. (Yes, it is true that Null <> Null in Access). So as a beginner, try to avoid solutions involving nulls. Having two different SQL strings and pick one or the other based on some form selection is probably the easiest to implement and conceptually the easiest to understand. Fancier queries? Not necessary.
 

Users who are viewing this thread

Top Bottom