Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


MS Access Select Query SQL with OR in two different fields & AND

Posted on 2014-03-12
Medium Priority
Last Modified: 2014-03-13
Can any one correc the following MS Access SQL. The output should result in dataset
that would meet the criteria of rrcode field = 1 OR osreportcode =1 & AND with other field criteria.  The following code is not giving the desired output

string sqldemolablh = "SELECT * from tbldemolab WHERE freezecode=1  AND wtcode=0 AND (rrcode=1 OR osreportcode=1)  order by pid ";
Question by:Srinivas Mantha
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
  • Learn & ask questions

Assisted Solution

COACHMAN99 earned 500 total points
ID: 39925604
"SELECT * from tbldemolab WHERE ( freezecode=1  AND wtcode=0 AND rrcode=1) OR (freezecode=1  AND wtcode=0 AND osreportcode=1)  order by pid ";
LVL 17

Accepted Solution

Kent Dyer earned 500 total points
ID: 39925625
I think this what you are asking for..

Think of it this way:  Tommy and Betty and Suzy - so the condition has to be where all three are in the result set (limiting results).
Tommy or Betty or Suzy - so the condition has to be where all there own independent result set (expanding results).

You are asking..

Tommy and Betty and Suzy
David and Betty and Suzy

the results are going to be with two different sets: Tommy, Betty, Suzy in the first set as well as: David, Betty, Suzy

Let's re-write your query:
FROM   tbldemolab
WHERE  ( ( rrcode = 1
           AND freezecode = 1
           AND wtcode = 0 )
          OR ( osreportcode = 1
               AND freezecode = 1
               AND wtcode = 0 ) )
ORDER  BY pid  

Open in new window

LVL 29

Expert Comment

ID: 39925638
Your SQL statement looks fine except for the semicolon you have after the ending quote.  You should put the semicolon inside the quoted string or dispense with it altogether.
string sqldemolablh = "SELECT * from tbldemolab WHERE freezecode=1 AND wtcode=0 AND (rrcode=1 OR osreportcode=1) order by pid;"

Open in new window


Author Closing Comment

by:Srinivas Mantha
ID: 39925781
Prompt and timely solution. That semicolon is actually an error here. I was coding through ASP.NET c# defining the string for SQL. Now the code is
sqldemolablh = " SELECT * FROM   tbldemolab WHERE  ( ( rrcode = 1 AND freezecode = 1  AND wtcode = 0 )  OR ( osreportcode = 1 AND freezecode = 1  AND wtcode = 0 ) ) ORDER  BY pid ";
LVL 29

Expert Comment

ID: 39926398
I don't understand why the change was needed. Both WHERE comparisons are logically equivalent.  Look at these 2 sentences:

Can Betty and Suzy and (Tommy or David) join our team?
Can (Betty and Suzy and Tommy) or (Betty and Suzy and David) join our team?


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question