Using And with Or

[The_Doc_Man]

Good catch, Cronk. There is an extra apostrophe in the section with the 2nd asterisk.

But looking at the original code, I had a V8 moment. (The bruise on my forehead is still healing.)

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*"

This will NEVER work because it is using the same quoting characters INSIDE the quoted SQL string as it is using around the string itself. This is why apostrophes are so important.

Searchstr = "[LatinName] Like '*plant*' or [CommonName] Like '*plant*' or ....

 

[Dave E]

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.

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 ...

No, my origial searchstr, without the addtional And condition worked fine, and still does.
Pat's solution of parethesising that whole Or code worked too.

Thanks for your input.

 

[Dave E]

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.

I used XOR in a database a long time ago when I found an OR operation failed. XOR will only return true if one of the conditions is true but not both.

Good catch, Cronk. There is an extra apostrophe in the section with the 2nd asterisk.

But looking at the original code, I had a V8 moment. (The bruise on my forehead is still healing.)

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*"

This will NEVER work because it is using the same quoting characters INSIDE the quoted SQL string as it is using around the string itself. This is why apostrophes are so important.

Searchstr = "[LatinName] Like '*plant*' or [CommonName] Like '*plant*' or ....

 

[Dave E]

Thanks for all your comments. The code now runs perfectly.
Much appreciated.

DaveE

 

[Pat Hartman]

Pat's solution of parethesising that whole Or code worked too.

Parentheses let you control the order of precedence

 

[CJ_London]

I agree with Cronk - as shown you get a syntax error - but think just changing the double quotes to single would do the trick if you are looking for the phrase 'plant'

Searchstr = "[LatinName] Like '*plant*' or ...

as far as the and/or is concerned put the brackets around your complete OR statement - assuming your code was correct (which syntactically it isn't) then it would be

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*") AND [Archived] = False

see the big red parentheses