Hello, I am trying to sort the output of a query on the results of 2 fields. The first field is Money owed, i’ve sorted this with > ‘0’ in the criterion section of the field.
The second field is a calculation based on 2 fields in my table. The 2 fields are both yes/no fields. I am subtracting one field from the other. Depending on the state of the fields 3 outcomes are possible: 1, 0 & -1.
If I leave the criterion empty in the second field all Monies owed are returned.
If I put =’0’ in the criterion of the second field only Monies owed with a ‘0’ are returned.
However if I put in the criterion field = ‘0’ OR = ‘1’ all of the Monies owed are returned that have a value of ‘0’ and ‘1’ irrespect of whether the value of the Monies owed is greater than 0.
I want returned all Monies owed that have a value greater than ‘0’ in the first field and equal to 0 or 1 in the second and not show the -1 Monies owed. Can someone help please?
Below is an example of the 2 fields. I need the monies owed field greater than 0 and the indate field to show all 0 and 1’s that correspond to Monies >0
Monies owed indate
£98.67 -1
£98.67 -1
£98.67 1
£98.67 0
£98.67 0
£98.67 1
£98.67 1
£98.67 1
£98.67 1
Thanks in advance
Chem