[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Bracketing in filter string

Posted on 2013-01-11
Medium Priority
236 Views
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]))
``````
this fails

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

Not sure why.
0
Question by:PeterBaileyUk
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2
• +2

LVL 66

Expert Comment

ID: 38767218
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

LVL 61

Accepted Solution

mbizup earned 668 total points
ID: 38767231
The order of operations works like it does in arithmetic

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

LVL 48

Expert Comment

ID: 38767236
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

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

Author Comment

ID: 38767259
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

LVL 61

Expert Comment

ID: 38767271
-->> ([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

Author Comment

ID: 38767299
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
``````
0

LVL 48

Assisted Solution

Dale Fye earned 668 total points
ID: 38767330
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

LVL 40

Assisted Solution

als315 earned 664 total points
ID: 38767931
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month13 days, 9 hours left to enroll