Help building a query based on user input (1 Viewer)

[brharrii]

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

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;

 

[Pat Hartman]

The problem is that your criteria includes both AND and OR operators and your parentheses aren't properly placed. Access generates useless parens so start by deleting ALL of them. Then go back and place them where they need to be to have the expression evaluated as you intend.

Logical expressions as with arithmetic expressions have an order of presidence and in both cases parentheses are used to alter the default order. Examine the expressions below to see the problem:
(A = B And C = D) Or F = G
is different from
A= B And (C=D Or F=G)

 

[brharrii]

Perfect, that worked perfectly, thank you!