• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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.
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
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
 
mbizupCommented:
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
 
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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 FyeCommented:
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
 
als315Commented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now