The result of a series of ANDs and ORs depends upon the order in which they are executed:
True OR anything AND False = False if executed left to right (OR with higher priority);
False AND anything OR True = True if executed left to right (AND with higher priority)
When building a filter I have had times when I would have liked to be able to parenthesise parts of an expression to ensure proper calculation of the result, eg searching transactions:
Where 'Transaction deleted flag' = 'No' And ('Date' >= '01/01/2021' And 'Date' <= '31/12/2021') And ('Fund number' = '001 General' Or 'Fund number' = '002 Designated') And ('Department number' = '0 - General' Or 'Department number' = '1 - Area 41')
The filter will generate the expression:
Where 'Transaction deleted flag' = 'No' And ('Date' >= '01/01/2021' And 'Date' <= '31/12/2021' And 'Fund number' = '001 General' Or 'Fund number' = '002 Designated' And 'Department number' = '0 - General' Or 'Department number' = '1 - Area 41')
Which gives me all the Department 0 entries that are in Fund 002 along with all the entries in Department 1 regardless of Fund plus all entries in Fund 001 with a department >1 - totally useless for my purpose.
To get round this I have to do multiple versions with different single entries for the Or clauses (4 different filters in this case), export to excel and merge the exported data.
Similarly I've had to do multiple filters to be able to extract data for a range of departments or Funds - the only options available are "Equal to" or "Not equal to". There is no "less than", etc like there is for a date range.
Guest
