Hello,
I'm trying to build a query that has two "And" Criteria and ten "Or" criteria. This is for a search box that I have built. I have tried several approaches but for some reason the two "And" criteria are bypassed. I was thinking to try building a query to filter the two And criteria first and then building another query with the Or criteria. But this is not working as well. Does anyone have any experience with this?
SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project, qrySapMhrs.SumOfPlannedHours, SumScore.pctComp FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (((tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) LEFT JOIN SumScore ON tblOrderDetails.OrderNum = SumScore.OrderNum) LEFT JOIN qrySapMhrs ON tblOrderDetails.OrderNum = qrySapMhrs.OrderNum) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification
WHERE (((tblProjectStatus.DateClosed) Is Null)
And ((tblOrderDetails.OrderNum) Is Not Null))
Or (((tblOrderDetails.OrderNum) LIKE '*TA1*')
And ((tblOrderDetails.WBS) LIKE '*TA1*')
And ((tblOrderDetails.Sortfield) LIKE '*TA1*')
And ((tblObjectStatus.Status) LIKE '*TA1*')
And ((tblOrderNotifications.Notification) LIKE '*TA1*')
And ((tblOrderDetails.Revision) LIKE '*TA1*')
And ((tblOrderDetails.OrderType) LIKE '*TA1*')
And ((tblOrderNotifications.CreatedBy) LIKE '*TA1*')
And ((tblOrderDetails.PlannerGroup) LIKE '*TA1*')
And ((tblOrderStatus.AssignUsername) LIKE '*TA1*'))
Order BY tblOrderDetails.Revision ASC