Selecting ALL persons in my combobox for query criteria

[Guest_imported]

I have a form with a combobox that has employees' names in it. I am using the following in my query to select an individual and then run the report with the name as the criteria:

[Forms]![IndividualFundingReport]![EmployeeName]

(As a note: the input is an employee number, not the actual employee name)

This works fine but I would also like to have the ability to select "ALL" so that I can run a general report on all the employees together. How can I accomplish this?

 

[TheInsider]

Obviously your combobox doesn't have a control source (because of the way that you are using it) - so you can have it left blank. Here is your criteria:

Like IIf(IsNull([Forms]![IndividualFundingReport]![EmployeeName]),"*",[Forms]![IndividualFundingReport]![EmployeeName])

Rob Marriott
rob@career-connections.net

 

[Guest_imported]

Thanks!

 

[Guest_imported]

After trying this a few times, it turns out to NOT work.

Now, if I leave it blank, it will run an ALL report but it will no longer run an individual report. If I select an individual, it still runs an ALL report.

What gives?

 

[Malaviya]

You can do the following
1. Cange the combobox property - Limit to listed vales = False
2. in the query criterian use the 'Like' operator.
3. Type '*' in the combo box to print the report for all employees. U can even use a wield card combination to select a group of employees also.

 

[PaulF]

add another field to your query, for the Name use

Exp1:[Forms]![IndividualFundingReport]![EmployeeName]

For the criteria [on a separate row ( Or: ) from the one you used for Employee Number]


Is Null

PaulF

 

[Guest_imported]

Thanks for the response.

Malavia: I cannot set my Limit to List as No because the combobox is actually 2 columns - one with employee id# and the other has the employee name. The bound column is the employee id# but the only visible column is the employee name. You cannot set the Limit to List as no under these conditions.

Paul F: I'll give that a try.

 

[Malaviya]

Hi

A new Idea came to my mind. Since U'r facing problems with Null in Paul's case, instead of using IsNull try using NZ function.
NZ is a function using which U can assign a default value to your expression in case it evaluates to null. I hope this will solve the problem.

best of luck.