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.
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
ASKER
Thanks again for all your help!
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