I have a database that manages multiple task lists. Each task on the task list is assigned to one of several individuals. I need the query to request the user to input the name of one of the task workers to then populate a list of all open tasks for that worker.
A task is considered open when it is lacking an estimated complete date, an actual complete date, a verified by name, or a verified by date. If anyone of these fields is left blank, the task should still be considered open.
Also if a task is considered "ongoing", "Non GMP", "Completed", or "Duplicate" (yes / no fields) it should not be returned on an open task list.
I've successfully been able to run a query that returns all open tasks based on this criteria, but when I try to add the part in that requests a name to pull up all tasks assigned to an individual, nothing changes. The dialog box comes up asking for the task workers name and then the query returns all open tasks regardless of who it is assigned to.
Does anyone have any ideas on this?
Thanks!
Bruce
A task is considered open when it is lacking an estimated complete date, an actual complete date, a verified by name, or a verified by date. If anyone of these fields is left blank, the task should still be considered open.
Also if a task is considered "ongoing", "Non GMP", "Completed", or "Duplicate" (yes / no fields) it should not be returned on an open task list.
I've successfully been able to run a query that returns all open tasks based on this criteria, but when I try to add the part in that requests a name to pull up all tasks assigned to an individual, nothing changes. The dialog box comes up asking for the task workers name and then the query returns all open tasks regardless of who it is assigned to.
Does anyone have any ideas on this?
Thanks!
Bruce
Code:
SELECT tblDiscrepancy.DiscrepancyAssignedTo, tblArea.AreaDesc, tblDiscrepancy.DiscrepancyDesc, tblDiscrepancy.DiscrepancyCorrectiveAction, tblDiscrepancy.DiscrepancyEstimatedCompDate, tblDiscrepancy.DiscrepancyActualCompDate, tblDiscrepancy.DiscrepancyVerifiedBy, tblDiscrepancy.DiscrepancyVerifiedDate, tblDiscrepancy.DiscrepancyRootCause, tblDiscrepancy.DiscrepancyOngoing, tblDiscrepancy.DiscrepancyNonGMP, tblDiscrepancy.DiscrepancyCompleted, tblDiscrepancy.DiscrepancyDuplicate
FROM (tblArea RIGHT JOIN tblObservedHeader ON tblArea.AreaID = tblObservedHeader.ObservedArea) RIGHT JOIN (tblAuditStaff RIGHT JOIN tblDiscrepancy ON (tblAuditStaff.AuditStaffID = tblDiscrepancy.DiscrepancyVerifiedBy) AND (tblAuditStaff.AuditStaffID = tblDiscrepancy.DiscrepancyAssignedTo)) ON tblObservedHeader.ObservedID = tblDiscrepancy.DiscrepancyTest
WHERE (((tblDiscrepancy.DiscrepancyAssignedTo)=[Assigned To]) AND ((tblDiscrepancy.DiscrepancyEstimatedCompDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False)) OR (((tblDiscrepancy.DiscrepancyActualCompDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False)) OR (((tblDiscrepancy.DiscrepancyVerifiedBy) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False)) OR (((tblDiscrepancy.DiscrepancyVerifiedDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False))
ORDER BY tblDiscrepancy.DiscrepancyAssignedTo, tblArea.AreaDesc;