Using And with Or

[Dave E]

Hi all,
I have a VBA search string, called searchstr, which looks through all records for a key word. It works fine.
The string uses all 'Or ' as in:

Searchstr = "[LatinName] Like "*plant*" or [CommonName] Like "*plant*" or [Plantedin] Like "*plant*" or [PlantDesc] Like "*plant*" or [HardyRef] Like "*plant*" or [PlantType] Like "*plant*" or [Colour] Like "*plant*" or [Light] Like "*plant*" or [Cultivation] Like "*plant*" or [Propagation] Like "*plant*" or [Pruning] Like "*plant*" or [Pests] Like "*plant*" or [Diseases] Like "*plant*" or [Comments] Like "*plant*"

Now, I want to include an 'And' to provide an option to include archived records or not, as in:

[HardyRef] Like "*plant*" or [PlantType] Like "*plant*" or [Colour] Like "*plant*" or [Light] Like "*plant*" or [Cultivation] Like "*plant*" or [Propagation] Like "*plant*" or [Pruning] Like "*plant*" or [Pests] Like "*plant*" or [Diseases] Like "*plant*" or [Comments] Like "*plant*" And [Archived] = False

But it appears to ignore the Archive condition.

All of the fields are in the one table.

Any ideas as to why that might be? Is it not possible to use Or and And in the same string search? Or is my syntax incorrect?

Thanks

Dave E

 

[theDBguy]

Hi. To make your AND/OR logic clear to the search engine, you could enclose the groups you want to be evaluated together inside a pair of parentheses. For example:

(Something OR SomethingElse) AND (AnotherThing)

or

Something AND (SomethingElse OR AnotherThing)

and one more...

(Something AND SomethigElse) OR AnotherThing

Hope that makes sense...

 

[Dave E]

Hi DBguy

Yes, I tried putting the additional condition in parentheses -"....or [Diseases] Like "*plant*" or [Comments] Like "*plant*" And (([Archived] = False))"

But that didn't work.

I'm sure it's something simple and I'll kick myself when I'm told the correct method...

 

[plog]

Now, I want to include an 'And' to provide an option to include archived records or not, as in:

There is no "option" to include archived records with your criteria:

... AND [Archived]=False

means that if a record is archived (=True) it will never be in your results. This really has nothing to do with syntax.

Without that criterion (your first version) the Archived status didn't matter--archived and unarchived would pass through to the results. When you added the criteria it ensured only unarchived records would pass through (assuming they meet the other criteria),

Perhaps you can demonstrate what you want to occur with data. Give us some sample data from your database and then some situations to help us understand how you want to query it.

 

[Gasman]

You do not have any around the and block?

 

[Pat Hartman]

@Dave E you didn't use the parentheses correctly. Let me explain the situation more clearly. The logic operators NOT, AND, and OR have an assigned order of precedence - in that order.
Your expression
"....or [Diseases] Like "*plant*" or [Comments] Like "*plant*" And (([Archived] = False))"
is being evaluated as
"....or [Diseases] Like "*plant*" or ([Comments] Like "*plant*" And (([Archived] = False)))" -- it is saying that comments must be like plant AND archived needs to be false OR any of the OR's can be true --you put lots of parens around the Archived = False but that isnot where they needed to go. Try this:
".(...or [Diseases] Like "*plant*" or [Comments] Like "*plant*") And [Archived] = False" That groups the OR's so that any one of them can be true AND Archived = False.

To alter the order in which the expression is evaluated, you would use parentheses. Take the following statement

a = b and c = d or e = f

Since AND has precedence over OR, the statement will be evaluated this way:

(a = b and c = d) or e = f -- so both the first two conditions have to be true or only the third condition has to be true. In your case any of the OR's can be true plus the AND needs to be true so you would use parentheses to tell the expression evaluator how YOU want the expression to be evaluated. So:

a = b and (c = d or e = f) -- now the first expression has to be true but EITHER of the ORs need to be two

So, when ever you see an expression that includes AND's and OR's and NOT's put parentheses around the part with the AND to see how the expression will be evaluated without parentheses.

a = b OR c = d AND e = f
becomes
a = b OR (c = d AND e = f)

a = b OR Not c = d AND e = f
becomes
a = b OR ((Not c) = d AND e = f)
and you might want:
a = b OR ((Not c = d) AND e = f)
To avoid confusion ALWAYS use parentheses whenever a complex expression contains two or more different relational operators.

Parentheses takes precedence over all the relational operators so that is how you control which expressions get evaluated first.

This the same concept as the arithmetic operators **, *, /, +, -

That is their order of precedence so

a + b * c + d/f = a + (b*c) + (d/f)

If you want something different, you need to tell the expression engine:

((a + b) * c) + (d/f)

 

[Dave E]

There is no "option" to include archived records with your criteria:

... AND [Archived]=False

means that if a record is archived (=True) it will never be in your results. This really has nothing to do with syntax.

Without that criterion (your first version) the Archived status didn't matter--archived and unarchived would pass through to the results. When you added the criteria it ensured only unarchived records would pass through (assuming they meet the other criteria),

Perhaps you can demonstrate what you want to occur with data. Give us some sample data from your database and then some situations to help us understand how you want to query it.

I wanted to search all the fields in a table for a keyword, as shown above.
The code uses Or to allow it to find all instances of the keyword in the record. It works fine in the first example I submitted. Then I wanted to include or exclude archived records whether they contained the keyword or not.
However, adding the And option doesn't appear to make any difference to the search, even if the [Archived] = False condition is placed within parathenses.

 

[Dave E]

@Dave E you didn't use the parentheses correctly. Let me explain the situation more clearly. The logic operators NOT, AND, and OR have an assigned order of precedence - in that order.
Your expression
"....or [Diseases] Like "*plant*" or [Comments] Like "*plant*" And (([Archived] = False))"
is being evaluated as
"....or [Diseases] Like "*plant*" or ([Comments] Like "*plant*" And (([Archived] = False)))" -- it is saying that comments must be like plant AND archived needs to be false OR any of the OR's can be true --you put lots of parens around the Archived = False but that isnot where they needed to go. Try this:
".(...or [Diseases] Like "*plant*" or [Comments] Like "*plant*") And [Archived] = False" That groups the OR's so that any one of them can be true AND Archived = False.

To alter the order in which the expression is evaluated, you would use parentheses. Take the following statement

a = b and c = d or e = f

Since AND has precedence over OR, the statement will be evaluated this way:

(a = b and c = d) or e = f -- so both the first two conditions have to be true or only the third condition has to be true. In your case any of the OR's can be true plus the AND needs to be true so you would use parentheses to tell the expression evaluator how YOU want the expression to be evaluated. So:

a = b and (c = d or e = f) -- now the first expression has to be true but EITHER of the ORs need to be two

So, when ever you see an expression that includes AND's and OR's and NOT's put parentheses around the part with the AND to see how the expression will be evaluated without parentheses.

a = b OR c = d AND e = f
becomes
a = b OR (c = d AND e = f)

a = b OR Not c = d AND e = f
becomes
a = b OR ((Not c) = d AND e = f)
To avoid confusion ALWAYS use parentheses whenever a complex expression contains two or more different relational operators.

Parentheses takes precedence over all the relational operators so that is how you control which expressions get evaluated first.

This the same concept as the arithmetic operators **, *, /, +, -

That is their order of precedence so

a + b * c + d/f = a + (b*c) + (d/f)

If you want something different, you need to tell the expression engine:

((a + b) * c) + (d/f)

Thanks, I'll go away and try that out.[/USER]

 

[theDBguy]

Then I wanted to include or exclude archived records whether they contained the keyword or not.

If I read that correctly, you may actually have two conditions there. For example, if you wanted to include archived records whether they contain any of the keyword or not, you might end up having something like this:

Criteria1 OR Criteria2 OR Criteria3 OR Archived=True

Now, if you want to exclude archived records from the results, then you might need something like this:

(Criteria1 OR Criteria2 OR Criteria3) AND Archived=False

 

[Dave E]

Thank you for your replies. When my grandaughter has gone home I will try all the different methods. I'm just pleased that it's possible.

DaveE

 

[Pat Hartman]

This is the only link I could find that actually talks even briefly about order of precedence and doesn't mention Not at all. Most of the articles on Boolean logic quickly devolve into truth tables (which are actually very useful to ensure you cover all outcomes when you have more than two variables to test) or algebraic notation which just puts non math geeks to sleep.

Research Guides: Boolean Operators: A Cheat Sheet: Home

Boolean logic (named after mathematician George Boole) is a system of logic to designed to yield optimal search results. The Boolean operators, AND, OR, and NOT, help you construct a logical search.

libguides.umn.edu

 

[Dave E]

This is the only link I could find that actually talks even briefly about order of precedence and doesn't mention Not at all. Most of the articles on Boolean logic quickly devolve into truth tables (which are actually very useful to ensure you cover all outcomes when you have more than two variables to test) or algebraic notation which just puts non math geeks to sleep.

Research Guides: Boolean Operators: A Cheat Sheet: Home

Boolean logic (named after mathematician George Boole) is a system of logic to designed to yield optimal search results. The Boolean operators, AND, OR, and NOT, help you construct a logical search.

libguides.umn.edu

Thanks for that, Pat. I will fix the code with the advice from this forum and move on. My Boolean algebra faded away very quickly after I completed my apprenticeship in the 70s. I doubt I will try to relearn it all again.

DaveE

 

[The_Doc_Man]

This is the only link I could find that actually talks even briefly about order of precedence and doesn't mention Not at all. Most of the articles on Boolean logic quickly devolve into truth tables (which are actually very useful to ensure you cover all outcomes when you have more than two variables to test) or algebraic notation which just puts non math geeks to sleep.

Research Guides: Boolean Operators: A Cheat Sheet: Home

Boolean logic (named after mathematician George Boole) is a system of logic to designed to yield optimal search results. The Boolean operators, AND, OR, and NOT, help you construct a logical search.

libguides.umn.edu

Pat, according to this reference, NOT is higher precedence than any other Boolean operator but lower in precedence than any math operator.

VBA Syntax - Precedence

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

 

[Pat Hartman]

Let's not confuse the issue. I think we are talking about Boolean operators in this thread. I also didn't mention the XOR because nobody knows what that even does But thinks for the link. I'll include it in the document/video I am working on.

 

[The_Doc_Man]

I was merely answering the implied question related to: "This is the only link I could find that actually talks even briefly about order of precedence and doesn't mention Not at all."

XOR is easy enough. It is an either (but not BOTH) whereas an OR is an either or both. It is the the difference between the two legs of a formal IF/THEN/ELSE/END IF situation. You execute the THEN leg or the ELSE leg but not both. You perform the XOR evaluation of the IF expression to choose one of TRUE and FALSE in order to execute one or the other but not both of the options. Therefore you could say that XOR is actually a very common thing - but not commonly recognized as such.

 

[Pat Hartman]

Thanks Doc. I do know what the XOR does. It also does bitwise compares if the operands are numeric rather than True/False

 

[The_Doc_Man]

OK, should have realized you WOULD know... but you are right. Most folks who don't come from a background that includes formal logic classes or a formal programming class or a lot of experience would not appreciate the other VBA logic functions like XOR and EQV. Or an oddball I once found, IMP (implies), in an advanced version of BASIC (not VBA-related).

 

[Pat Hartman]

I've never actually used an XOR except to do a bitwise compare The point of the paper/video is actually order of precedence not the relational operators since that is where people get in trouble. Taken individually, people know what AND and OR are. They get in a little more trouble with NOT because they're inclined to think that "Not a and b" means "Not(a AND b)" rather than "Not(a) AND b". So, I'm going to try to clear that up.

 

[The_Doc_Man]

True, NOT gives trouble of all sorts. Are you even going to touch on DeMorgan's Theorem as a way to invert the sense of AND, OR, and NOT combos?

 

[Cronk]

Re #1
"Searchstr = "[LatinName] Like "*plant*"..... works fine"

Seems to me this will generate a syntax error

Maybe something like
Searchstr = "[LatinName] Like '*" & plant & "'*' or ...