Brackets!!! AHHHHH!

[WackyWaterGuy]

Hi Everyone! I have been having a problem, and for the life of me, can't figure this out. Maybe one of you smart people can help me out. I am writing a dynamic query in SQL, based on checkboxes. I am having no problem linking the checkboxes back etc...but I am having problems creating the query..to an extent anyways. Here is my problem:

As an example:

MySearch = ([Shift] = A 'or' [Shift] = B)
Section = ([Location] = Kitchen)

My code that puts these together is as follows:

        section = ((MySearch) & " AND" & Section)

Then "section" is queried further down in the code!

So here is the problem: When the query is created (which I can view in design mode), it actual does this:

([Shift] =A) OR (([Shift] = B AND [Location]= Kitchen)

This is not what I want

I would like: ((([Shift] =A) OR ([Shift] = B)) AND [Location]=Kitchen)

Can someone please help!

I have tried numerous iterations putting brackets in many different place..with no luck! Can someone please help!

I would really appreciate it!

Thank you!

 

[Mile-O]

AND Kitchen what? - you have no criteria for the kitchen...

 

[WackyWaterGuy]

Sorry...My mistake...I have corrected it in the original post!

Thanks!

 

[Mile-O]

MySearch = "((([Shift]) = 'A') OR (([Shift]) = 'B'))"
Section = "(([Location]) = 'Kitchen')"


 section = MySearch & " AND " & Section

 

[WackyWaterGuy]

Hi there! I can see what you are saying, but it doesn't seem to be working. The difficulty comes in the fact that we cannot simply set the code to the way that you have desribed. Depending on what checkboxes are chosen changes the statements to build the query. From what I can figure out, and AND function is taking precidence over the OR function. So when the query is being created, the AND statemtent is being done first....not second.

Here is the SQL code (taken directly from the automatic query being built):

'---This is the code that is created....that we DON'T want!
SELECT *
FROM [Data Output]
WHERE ((([Data Output].[Shift])='A')) OR ((([Data Output].[Shift])='B') AND (([Data Output].Location)='Kitchen'));


'---Editing the query in design view...this is the SQL code created....that we want!!
SELECT *
FROM [Data Output]
WHERE ((([Data Output].[Shift])='A' OR ([Data].[Shift])='B') AND (([Data Output].Location)='Kitchen'));

Is there anyway to make the OR function take presedence (ie. occur first, before the AND operation???

Please get back to me someone!

Thanks!

 

[Jon K]

The SQL statement is the same as:-

SELECT *
FROM [Data Output]
WHERE [Shift] IN ('A','B') AND [Location]='Kitchen'


It eliminates the OR. Can it be coded like that?

 

[WackyWaterGuy]

I was able to re-work my code to output that way....and it worked!


Thank you very much!

You made my day

Thanks again

WWG