Bracketing in filter string

The following filetrs work singulary but if I combine them with an 'AND' they dont work correctly:

this works:
((([Bodystyle]>='04' AND [Bodystyle]<='05') OR ([Bodystyle]='21')) OR (isnull([Bodystyle])))
this works

([Enginecapacity]>=1397 AND [Enginecapacity] <= 1397 Or Isnull([Enginecapacity]))

Open in new window

this fails

((([Bodystyle]>='04' AND [Bodystyle]<='05') OR ([Bodystyle]='21')) OR (isnull([Bodystyle]))) and ([Enginecapacity]>=1397 AND [Enginecapacity] <= 1397 Or Isnull([Enginecapacity]))

Open in new window


Not sure why.
PeterBaileyUkAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
The order of operations works like it does in arithmetic

ORs are treated like addition
ANDs are treated like multiplication

In order for ORs to take precedence over ANDs, you need to group them the same way you would group addition and multiplication:

Arithmetic:

(1 +2)  * 3 = 9
1+ 2*3 = 7

Ands/Ors, these two will have different results as well:

X AND Y OR Z
X AND (Y OR Z)


The only difference is that ANDS/ORs are based on boolean values (-1 and 0) in your Access databases
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Spell out for us in great detail how it fails.  An error message, doesn't return records, ...

Also, make sure you have your order of precedence ( )'s correct, as it looks like your filter now has a lot of expressions to deal with.
0
 
Dale FyeCommented:
Peter, can you state verbally what you are trying to accomplish?  

There seem to be some flaws with your logic.

For example, what are you trying to achieve with:

([Bodystyle]>='04' AND [Bodystyle]<='05')

I believe you could accomplish all of your [BodyStyle] needs (including the IsNull() with the simple statement:

NZ([Bodystyle], '04') IN ('04', '05', '21)

Which would leave you with:
 
 ([Enginecapacity]>=1397 AND [Enginecapacity] <= 1397 Or Isnull([Enginecapacity]))

But this: [Enginecapacity]>=1397 AND [Enginecapacity] <= 1397

will only evaluate to True if [EngineCapacity] = 1397, so why not just use:

Val(NZ([EngineCapacity], 1397)) = 1397

The whole criteria would read:

NZ([Bodystyle], '04') IN ('04', '05', '21) AND val(NZ([EngineCapacity], 1397)) = 1397

You may not need the Val() function in that syntax, but if this is going to be used in a query, the NZ() function will always return a string, so you need to use the Val() function to convert it back to a numeric.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
PeterBaileyUkAuthor Commented:
their is variance on the engine capacity in the string i gave a variance of zero was set.

([Bodystyle]>='04' AND [Bodystyle]<='05') i wanted just bodystyle codes 04 and 05.

I will change to use the IN style.

the code doesnt give an error it just returns records I never expected.

I will try the 'IN'

it will make the filter simpler.
0
 
mbizupCommented:
-->> ([Bodystyle]>='04' AND [Bodystyle]<='05')

When you're dealing with exact matches in the same field, the correct operator is OR.

(Bodystyle can't be equal to 04  AND 05 at the same time... it is 04 OR 05)

([Bodystyle] ='04' OR [Bodystyle]='05')
0
 
PeterBaileyUkAuthor Commented:
this is what i was doing as a whole depending how many filters are set it returns the filter string.:

If Not IsNull(Me.CBExportBody) Then
    Counter = Counter + 1
    Select Case Counter
        Case Is = 0
        Case Is = 1
                
                Select Case Me.CBExportBody.ListIndex
                    Case 0
                    'coupe /convertible / sports
                    
                    
                    strFilter = "((([Bodystyle]>='04' AND [Bodystyle]<='05') OR ([Bodystyle]='21'))" & " OR (isnull([Bodystyle])))"
                
                    
                    
                    
                    Case 1
                    'estate/mpv
                    strFilter = "(([Bodystyle]='06' OR [Bodystyle]='96')" & " OR (isnull([Bodystyle])))"
                    Case 2
                    'hatchback
                    strFilter = "(([Bodystyle]>='13' AND [Bodystyle]<='14')" & " OR (isnull([Bodystyle])))"
                    Case 3
                    'motorcycles
                    strFilter = "([Bodystyle]='72' OR [Bodystyle]='09' OR [Bodystyle]>='15' and [Bodystyle]<='19'" & " OR (isnull([Bodystyle])))"
                   
                    
                    Case 4
                    'saloon
                    strFilter = "(([Bodystyle]>='01' AND [Bodystyle]<='03')" & " OR (isnull([Bodystyle])))"
                    Case 5
                    'others
                    strFilter = "((([Bodystyle]>='07' AND [Bodystyle]<='08') OR ([Bodystyle]>='22' AND [Bodystyle]<='71'))" & " OR (isnull([Bodystyle])))"
                    
                   
                    
                End Select
                   
        
        Case Is > 1
               
                   Select Case Me.CBExportBody.ListIndex
                    Case 0
                    'coupe /convertible / sports
                    strFilter = strFilter & " and " & "((([Bodystyle]>='04' AND [Bodystyle]<='05') OR ([Bodystyle]='21'))" & " OR (isnull([Bodystyle])))"
                    Case 1
                    'estate/mpv
                    strFilter = strFilter & " and " & "(([Bodystyle]='06' OR [Bodystyle]='96')" & " OR (isnull([Bodystyle])))"
                    Case 2
                    'hatchback
                    strFilter = strFilter & " and " & "(([Bodystyle]>='13' AND [Bodystyle]<='14')" & " OR (isnull([Bodystyle])))"
                    Case 3
                    'motorcycles
                    strFilter = strFilter & " and " & "([Bodystyle]='72' OR [Bodystyle]='09' OR [Bodystyle]>='15' and [Bodystyle]<='19'" & " OR (isnull([Bodystyle])))"
                   
                    
                    Case 4
                    'saloon
                    strFilter = strFilter & " and " & "(([Bodystyle]>='01' AND [Bodystyle]<='03')" & " OR (isnull([Bodystyle])))"
                    Case 5
                    'others
                    strFilter = strFilter & " and " & "(([Bodystyle]>='07' AND [Bodystyle]<='08') OR ([Bodystyle]>='10' AND [Bodystyle]<='12') OR([Bodystyle]>='22' AND [Bodystyle]<='71')" & " OR (isnull([Bodystyle])))"
                    
                   
                    
                End Select
              
    End Select
End If

Open in new window

0
 
Dale FyeConnect With a Mentor Commented:
Not sure what you mean by: "their is variance on the engine capacity in the string i gave a variance of zero was set."

Do you mean there is another control where you enter the a EngineCapacityVariance, and you want everything within that variance.  If so, then you might use something like:

strFilter = "Abs([EngineCapacity] - " & Val(me.txtEngineCapacity) & ") <= " & NZ(me.txtEngCapVariance, 0)

This would subtract the EngineCapacity entered in your filter textbox from the value entered in your [EngineCapacity] field, and then would take the absolute value of that number and compare it to the variance you entered in that textbox.
0
 
als315Connect With a Mentor Commented:
Peter, if you like to have some groups, may be better to add column with type to table with Bodystyle, for example:
BodystyleID, BodyStyleName, BodyStyleGroup
04                      body1             1
05                      body2             1
06                      body3             2
Bodystyles:
BodyStyleGroupID          BodyStyleGroup
1                                      coupe /convertible / sports
2                                      estate/mpv
In this case filters will be very simple.
Do same for
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.