Fatal Jet Essentials Filtering error

If you ever work with me in trainings, I'm all about arming users with a system of rules and guidelines for building Jet Reports.  Filtering can be one of those tricky areas where oftentimes you don't know you're doing it wrong until it's too late.

Here's a rule:  Never mix OR and AND in the same filter criteria.  
And a second rule to go with it:  Never filter on the same filed twice.

Rule #2 - Never filter on the same field twice

Consider the function:
NL("Rows", "Customer", "Name"
     ,"Name", "A*"
     ,"Name", "*B")

What will Jet Return? All Customers starting with "A" OR ending with "B"?  Or All customers starting with "A" AND ending with "B"?  

Answer:  It doesn't matter.  Don't filter on the same field twice.  If you want A*|*B or A*B then do it in the same filter!

That rule is usually an easy one to follow.  But what about mixing AND and OR?

Rule #1 - Never mix OR and AND in the same filter criteria

Consider the function:
NL("Rows", "Customer", "Name"
     "Name", "A*|B*&<>*Corp*")

Will Jet return all Customers starting with "A" OR "B" that don't contain "Corp"?     
   (A*|B*) & <>*Corp*  ?  

Or all customers starting with "A" OR all customers starting with "B" that don't contain "Corp"? 
   A* | (B*&<>*Corp*)

Answer:  It doesn't matter.  Don't do it!  Theoretically, Jet should honor the order of operations as described by SQL, but even armed with that knowledge we still stand on shaky ground.  I certainly wouldn't stake my career on hoping I got the order right.

Solution:  Use NL("Filter") and Array calculations to get the results you want.  Please review this blog post for review on the technique, but the general gist of it is you'll make two sets of values and then compare the two sets.

If I want:  All Customers starting with "A" OR "B" that don't contain "Corp"? Then i create:


NP("Difference", 
,NL("Filter", "customer", "no.", "name", "A*|B*")
,NL("Filter", "customer, "no.", "name", "<>*Corp*"))


Remember, NP("Difference") says take the values in Array 1 and subtract values in Array 2.


If I want all customers starting with "A" OR all customers starting with "B" that don't contain "Corp"? 


NP("Union", 
,NL("Filter", "customer", "no.", "name", "A*")
,NL("Filter", "customer, "no.", "name", "B*&<>*Corp*"))

Although it may take a few extra seconds to execute, it's impossible to get the wrong result.  At the end of the day I'll have a list of Customer Numbers where all of the Names start with A Or B and none of the names contain Corp.