Can I use HAVING to filter for AND and OR of simple record set? (MS Access SQL)-CodePudding

I have a small record set of invoices. I'd like to extract all records 180 days old and newer for a statement. In some cases there are unpaid invoices over 180 days old that I ALSO want to display. When I added the second AND with (Orders.[OrderPaid])=NO) the 0-180 days records are not displayed. The 0-180 day criteria works perfect without the (Orders.[OrderPaid])=NO) addition. The (Orders.[OrderPaid])=NO) works without the 0-180 day criteria.

The code I have tried is is:

HAVING (((Customers.[CustomerID])=[Forms]![OrdersByCustomer]![CustomerID]) AND (DateAdd("d", -180, Now())<=Orders.[Invoice Date]) AND (Orders.[OrderPaid])=NO)

And: HAVING (((Customers.[CustomerID])=[Forms]![OrdersByCustomer]![CustomerID]) AND (DateAdd("d", -180, Now())<=Orders.[Invoice Date]) OR (Orders.[OrderPaid])=NO)

Could someone suggest the correct syntax or method?

CodePudding user response：

Parentheses are crucial here, it helps to remove the auto-generated ones and format the SQL.

HAVING Customers.[CustomerID] = [Forms]![OrdersByCustomer]![CustomerID]
  AND (
       DateAdd("d", -180, Now()) <= Orders.[Invoice Date]
       OR 
       Orders.[OrderPaid] = NO
      )

The main parentheses need to be around the 2nd and 3rd clauses (OR), not 1st and 2nd.

Reason: in SQL the priority of AND is higher than that of OR.