Select all query.....

[Geordie2008]

Afternoon all....

I have a form with a combo box which filters all of my queries. When a user selects a "manager" they get a list of all of the employees associated with that manager. This is because the user wanted to be ble to send individual "manager" reports to the managers concerned.

I designed 8 reports from these queries and now the user would like to be able to remove the filter to allow them to have a summary of all employees irrespective of manager.

Is there anyway I can use the existing queries / structure / reports to just return all when no filter is applied?

Currently the report will return blank results if a null value is passed as thre are no instances of a <NULL> manager field. When I actually want it to return everything when <null> is selected.... Or better still if I could have a "select all" option within the combobox

Thanks,
Mandy

 

[MSAccessRookie]

I assume your query contains something like

Select EmployeeTable.Employee
From EmployeeTable
Where EmployeeTable.Manager = [YourFormName]![ManagerComboBoxControlName]

If you change that to something like the following it might work

Select EmployeeTable.Employee
From EmployeeTable
Where ( ([YourFormName]![ManagerComboBoxControlName] = EmployeeTable.Manager)
OR ([YourFormName]![ManagerComboBoxControlName] Is Null) )

 

[Geordie2008]

Hi MSAccessRookie...

I think that this query would only bring back results where the manager is <NULL>

I actually want to ignore the filter in this case.

Usually I want the report to run for all employees where the manager = "Sam Smith" or "John Jones".

the user wants to be able to query for ALL managers... hence I no longer require the filter.

If I used if <NULL> filter I would get zero results.... if I used no filter I would get every result.

Is there any way to do this?

Thanks for your help,
Mandy

 

[MSAccessRookie]

I assume your query contains something like

Select EmployeeTable.Employee
From EmployeeTable
Where EmployeeTable.Manager = [YourFormName]![ManagerComboBoxControlName]

If you change that to something like the following it might work

Select EmployeeTable.Employee
From EmployeeTable
Where ( ([YourFormName]![ManagerComboBoxControlName] = EmployeeTable.Manager)
OR ([YourFormName]![ManagerComboBoxControlName] Is Null) )

The query should bring back any row when the form Manager choice matches the table manager choice ([YourFormName]![ManagerComboBoxControlName] = EmployeeTable.Manager), or any row when the form Manager choice is Null ([YourFormName]![ManagerComboBoxControlName] Is Null). The latter should bring back all rows, since there is no comparison being performed.

As ling as there uis a way to enter a Null Choice, I believe that it should work