Query to pull set criteria or all

  • Thread starter Thread starter Chantel33
  • Start date Start date
C

Chantel33

I have the following query:
SELECT [P682 Countsheet].Username, [P682 Countsheet].Task, [P682
Countsheet].Product, [P682 Countsheet].[Assign Date], [P682
Countsheet].[Description of Task], [P682 Countsheet].[Due Date], [P682
Countsheet].Status, ([Volume]-[Done]) AS Expr1, [Area to Task Query].Area
FROM [P682 Countsheet] INNER JOIN [Area to Task Query] ON [P682
Countsheet].Task = [Area to Task Query].[Task ]
WHERE ((([P682 Countsheet].Username)=[enter opid]) AND (([P682
Countsheet].Status)<>"Completed"));


I would like the query to ask for an OPID as i have set or if no OPID is
entered I would like it to pull all OPID's

Is this possible and if so How do i do this?
 
SELECT [P682 Countsheet].Username,
[P682 Countsheet].[Task],
[P682 Countsheet].[Product],
[P682 Countsheet].[Assign Date],
[P682 Countsheet].[Description of Task],
[P682 Countsheet].[Due Date],
[P682 Countsheet].[Status],
([Volume]-[Done]) AS Expr1,
[Area to Task Query].Area
FROM [P682 Countsheet] INNER JOIN [Area to Task Query]
ON [P682 Countsheet].Task = [Area to Task Query].[Task ]
WHERE [P682 Countsheet].Username
LIKE [enter opid or leave blank for all] & "*"
AND [P682 Countsheet].[Status] <>"Completed" ;
 
I would like the query to ask for an OPID as i have set or if no OPID is
entered I would like it to pull all OPID's
Click to expand...

One way to do this is to explicitly check for NULL in the parameter:

SELECT [P682 Countsheet].Username, [P682 Countsheet].Task, [P682
Countsheet].Product, [P682 Countsheet].[Assign Date], [P682
Countsheet].[Description of Task], [P682 Countsheet].[Due Date], [P682
Countsheet].Status, ([Volume]-[Done]) AS Expr1, [Area to Task Query].Area
FROM [P682 Countsheet] INNER JOIN [Area to Task Query] ON [P682
Countsheet].Task = [Area to Task Query].[Task ]
WHERE ((([P682 Countsheet].Username)=[enter opid] OR [enter opid] IS NULL)
AND (([P682 Countsheet].Status)<>"Completed"));
 
Thanks so much Jerry it work beautifully!

Jerry Whittle said:
SELECT [P682 Countsheet].Username,
[P682 Countsheet].[Task],
[P682 Countsheet].[Product],
[P682 Countsheet].[Assign Date],
[P682 Countsheet].[Description of Task],
[P682 Countsheet].[Due Date],
[P682 Countsheet].[Status],
([Volume]-[Done]) AS Expr1,
[Area to Task Query].Area
FROM [P682 Countsheet] INNER JOIN [Area to Task Query]
ON [P682 Countsheet].Task = [Area to Task Query].[Task ]
WHERE [P682 Countsheet].Username
LIKE [enter opid or leave blank for all] & "*"
AND [P682 Countsheet].[Status] <>"Completed" ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Chantel33 said:
I have the following query:
SELECT [P682 Countsheet].Username, [P682 Countsheet].Task, [P682
Countsheet].Product, [P682 Countsheet].[Assign Date], [P682
Countsheet].[Description of Task], [P682 Countsheet].[Due Date], [P682
Countsheet].Status, ([Volume]-[Done]) AS Expr1, [Area to Task Query].Area
FROM [P682 Countsheet] INNER JOIN [Area to Task Query] ON [P682
Countsheet].Task = [Area to Task Query].[Task ]
WHERE ((([P682 Countsheet].Username)=[enter opid]) AND (([P682
Countsheet].Status)<>"Completed"));


I would like the query to ask for an OPID as i have set or if no OPID is
entered I would like it to pull all OPID's

Is this possible and if so How do i do this?
Click to expand...
Click to expand...
 
Back
Top