And OR (1 Viewer)

CosmaL · Dec 6, 2019

Hello friends,

i have the following syntax in my query:

WHERE (((tblMaintenanceActions.FinishedDate) Between Date() And Date()+30) AND ((tblMaintenanceActions.Completed)=False)) OR (((tblMaintenanceActions.AApproval)=False)) OR (((tblMaintenanceActions.BApproval)=False));

I want to view the next 30 days records which must be or completed, or A approved or B approved.

With this syntax, i get records more than 30 days (ex april 2020).

Any idea?

Galaxiom · Dec 6, 2019

AND takes precedence over OR unless the parenthesis dictate otherwise.

theDBguy · Dec 6, 2019

Hi. In other words, your criteria parentheses should basically resolve to something this.

...WHERE (FieldName Between Date1 And Date2) AND (Criteria1 OR Criteria2 OR Criteria3)

gemma-the-husky · Dec 7, 2019

or even a version of this.

..WHERE (FieldName Between Date1 And Date2) AND NOT (Criteria1 AND Criteria2 AND Criteria3)

here, criteria 1 2 and 3 may be the opposite of the original. ie, sometimes it is easier to discard the full matches, rather than test for exclusions separately. so test for positives, rather than negatives.

theDBguy · Dec 8, 2019

gemma-the-husky said:
or even a version of this.

..WHERE (FieldName Between Date1 And Date2) AND NOT (Criteria1 AND Criteria2 AND Criteria3)

here, criteria 1 2 and 3 may be the opposite of the original. ie, sometimes it is easier to discard the full matches, rather than test for exclusions separately. so test for positives, rather than negatives.

Sometimes, yes; but sometimes, it depends.

So, be aware...

Micron · Dec 8, 2019

I don't get that comment either, but I'm also not sure what the pictures are supposed to mean. About a year ago, I got deep into Boolean operators and found that they're more complicated (or at least the use of them is) than what most people seem to realize. The results in the pic make sense to me:

(True OR False OR False) will return true if any of the inputs are True

Not (True AND False AND False) - NOT will return True if the input is False. Since something cannot be( true and false and false), it is false, thus it is True that it is NOT True.

(True AND True AND True) should be obvious that this is True

Not (True AND True AND True) The expression evaluates to True, thus it is False to say the expression does NOT result in True.

IIRC, when there are more than 2 expressions and OR is involved, it is more important how the operators are grouped. I'll venture to say that one operator that you seldom see is XOR, possibly because it's not broadly understood.

theDBguy · Dec 8, 2019

Micron said:
I don't get that comment either, but I'm also not sure what the pictures are supposed to mean. About a year ago, I got deep into Boolean operators and found that they're more complicated (or at least the use of them is) than what most people seem to realize. The results in the pic make sense to me:

(True OR False OR False) will return true if any of the inputs are True

Not (True AND False AND False) - NOT will return True if the input is False. Since something cannot be( true and false and false), it is false, thus it is True that it is NOT True.

(True AND True AND True) should be obvious that this is True

Not (True AND True AND True) The expression evaluates to True, thus it is False to say the expression does NOT result in True.

IIRC, when there are more than 2 expressions and OR is involved, it is more important how the operators are grouped. I'll venture to say that one operator that you seldom see is XOR, possibly because it's not broadly understood.

Hi Micron. The OP's requirement was originally stated as so:

CosmaL said:
I want to view the next 30 days records which must be or completed, or A approved or B approved.

Which could be read as, given a record is within the next 30 days, then show it if said record (1) needs to be completed, or (2) needs to be approved by/for A, or (3) needs to be approved by/for B.

So, if a record is within the next 30 days but needs all three (completed, approved A, and approved B), the question is, should it be shown/included in the result or not?

So, depending on the answer to that question, then the use of Not(C1 AND C2 AND C3) may or may not work some of the time.

Make sense?

The_Doc_Man · Dec 8, 2019

Even though there is such a thing as operator precedence, I always try to remember to use explicit parenthetical grouping rather than rely on (my very poor memory of) the rules of operator precedence.

gemma-the-husky · Dec 8, 2019

I was think more of changing this idea

WHERE (((tblMaintenanceActions.FinishedDate) Between Date() And Date()+30) AND ((tblMaintenanceActions.Completed)=False)) OR (((tblMaintenanceActions.AApproval)=False)) OR (((tblMaintenanceActions.BApproval)=False));

to this idea

WHERE (((tblMaintenanceActions.FinishedDate) Between Date() And Date()+30) AND NOT
(
((tblMaintenanceActions.Completed)=TRue)) AND (((tblMaintenanceActions.AApproval)=True)) AND
(((tblMaintenanceActions.BApproval)=TRue))
)

It seemed maybe easier as a general premise to me to resolve that the records you want are those within the date criteria and where some approval flag remains outstanding, but encompassing them all as NOT (true).

It's as broad as it's long in this case, I think, but with some complicated comparisons it's easier to reject the "true" items, then identify a "false" item.

(A similar approach is often taken to evaluating probabilities, where it's easier to evaluate the not true probability than the false probability)

theDBguy · Dec 8, 2019

gemma-the-husky said:
I was think more of changing this idea

WHERE (((tblMaintenanceActions.FinishedDate) Between Date() And Date()+30) AND ((tblMaintenanceActions.Completed)=False)) OR (((tblMaintenanceActions.AApproval)=False)) OR (((tblMaintenanceActions.BApproval)=False));

to this idea

WHERE (((tblMaintenanceActions.FinishedDate) Between Date() And Date()+30) AND NOT
(
((tblMaintenanceActions.Completed)=TRue)) AND (((tblMaintenanceActions.AApproval)=True)) AND
(((tblMaintenanceActions.BApproval)=TRue))
)

It seemed maybe easier as a general premise to me to resolve that the records you want are those within the date criteria and where some approval flag remains outstanding, but encompassing them all as NOT (true).

It's as broad as it's long in this case, I think, but with some complicated comparisons it's easier to reject the "true" items, then identify a "false" item.

(A similar approach is often taken to evaluating probabilities, where it's easier to evaluate the not true probability than the false probability)

Hi Dave. I think I understood what you were trying to say. But as I was saying earlier, that logic version may work some of the times, but other times, it may not work, for this case, because 1 OR 2 OR 3 is not really the same as NOT(1 AND 2 AND 3).

theDBguy · Dec 9, 2019

Just to clarify what I was trying to say, I created the following table.

Running the following query:

Code:

SELECT test.*
FROM test
  WHERE (TestDate Between Date() And Date()+30)
   AND (A=True OR B=True OR C=True);

produced the following result:

And running the following query:

Code:

SELECT test.*
FROM test
 WHERE (TestDate Between Date() And Date()+30)
   AND Not (A=True AND B=True AND C=True);

produced the following result:

So, all I'm saying is, in this case, the two logic versions are not exactly the same (or won't always have the same result).

Hope that makes sense...

And OR (1 Viewer)

CosmaL

Registered User.

Galaxiom

Super Moderator

theDBguy

I’m here to help

gemma-the-husky

Super Moderator

theDBguy

I’m here to help

Attachments

Micron

AWF VIP

theDBguy

I’m here to help

The_Doc_Man

Immoderate Moderator

gemma-the-husky

Super Moderator

theDBguy

I’m here to help

theDBguy

I’m here to help

Attachments

Similar threads

Users who are viewing this thread