Link to home
Start Free TrialLog in
Avatar of BobRosas
BobRosas

asked on

Specify One or All records in Dropdown

I want to either select “Null” from the dropdown for all Supervisor records or select a specific Supervisor from the dropdown.  If I fill in Supervisor it works but if I leave it null I get nothing…instead of all records.  What do I need to change so that one of the dropdown options allows the user to select all Supervisors?

My dataset code is...
SELECT        SupervisorID, Supervisor
FROM            vw_HrLkUpSupervisor
WHERE        (SupervisorID IN (@Supervisor)) OR
                         (@Supervisor IS NULL)
ORDER BY Supervisor

My Supervisor parameter code is...
SELECT DISTINCT SupervisorID, Supervisor
FROM            vw_HrLkUpSupervisor
UNION
SELECT        ' ALL' AS Supervisor, NULL AS SupervisorID
ORDER BY Supervisor

Sample data is…
NULL
ATKL3456
SOFT8765
BNAB9123

I have tried setting the Manager parameter to "Allow null value" and “Allow blank value”.
I have value field as SupervisorID and label as Supervisor.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

My usual method is something like this...

SELECT   -1 as SupervisorID, ' < ALL > ' as Supervisor
FROM MakeUpATableNameHere
UNIONvw_HrLkUpSupervisor
SELECT     SupervisorID, Supervisor
FROM            vw_HrLkUpSupervisor
WHERE        (SupervisorID IN (@Supervisor)) OR (@Supervisor IS NULL)   -- ?
ORDER BY Supervisor

Then whatever SQL/VB/? process this test for SupervisorID = -1.  If yes return all records, if it's a value then add that to the WHERE
Avatar of BobRosas
BobRosas

ASKER

Thank you for your help but I do not understand the code.  You are saying I need to create a temporary table to do this?  I tried just making up a table name tblTEMP and of course got the error that the table wasn't found.  
I don't understand the extra complexity.  I have a form where the user can either select one user or all.  The only difference I can see is that the query has a Where clause baasd on the supervisor…

This works for selecting all or one employee…
SELECT        LastName + ', ' + FirstName AS Name, IndID
FROM            vw_HrIndividuals
WHERE        (Manager IN (@Supervisor))
UNION
SELECT        ' All' AS Name, NULL AS IndId
ORDER BY Name

 So I tried adding your WHERE clause to my existing code without the temp table…

SELECT DISTINCT SupervisorID, Supervisor
FROM            vw_HrLkUpSupervisor
WHERE        (SupervisorID IN (@Supervisor)) OR
                         (@Supervisor IS NULL)
UNION
SELECT        ' ALL' AS Supervisor, NULL AS SupervisorID
ORDER BY Supervisor

It works in query design but when I run the report I get the error…
      Parameter ‘Supervisor’ has a DefaultValue or a ValidValue the depends on the report
      parameter ‘Supervisor”.  
Isn’t that because the code is referring to itself?

I thought the WHERE clause in the query that works just narrowed the scope of employees.    Since I don’t want to narrow the scope I just left it out and I don’t understand why it doesn’t work.

I do appreciate your help.  I’m sorry I’m not getting this.  Since I have a very similar example that works I’m trying to keep this just as simple.
ASKER CERTIFIED SOLUTION
Avatar of BigSchmuh
BigSchmuh
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That works!  Thank you so much!  I've maxd out points because I've been working on this for awhile and I'm so glad you found a resolution for me!
Thanks again for all your help!