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

Access 2007 Query and conditional statements

Sorry, I have a really basic understanding of Access...

I want to be able to use multiple conditions to pull specific data for my query. I know how to use the "where"  in expression builder, and my understanding is that if i use multiple "wheres" I will be creating "AND" STATEMENTS.

I want to know how to create a query based on conditional "OR" STATEMENTS across multliple variables.
For example- if varable1=x OR variable2=x OR variable3=x then include the record.  Is that possible to do? I know very little SQL...

I want to create a query that gets demographic data from the table "participant enrollment information"  but only if they entered one of the 7services (FS, FL, IDA, REAP, SSHF, CD DVRP) during a specific timeframe.

FSenterdt1 is between 1/1/2009 3/31/2009 OR FSenterdt2 is between 1/1/2009 3/31/2009 etc.

Sorry if it is unclear. I don't know how to better explain my dilemma.
I can email the database if that is easier too..
1 Solution
try attaching the file, it's too vage as it is
There is no dilema in your question. If you want to use OR operator in the WHERE simply place your criteria for given columns into line beginning by "or:" (just below the Criteria line) in Query designer. If you have more values for one column write them into one criteria line and concatenate them by Or operator.

So your service column will have following values in the Criteria line:
'FS' Or 'FL' Or 'IDA' Or 'REAP' Or 'SSHF' Or 'CD' Or 'DVRP'
or you may use IN operator:
IN ('FS'; 'FL'; 'IDA'; 'REAP'; 'SSHF'; 'CD'; 'DVRP')

Columns for date range will use between operator:
between 1/1/2009 And 3/31/2009

but it seems to combine And with Or  is not so easy in Query designer...

So, the simplest way is to write SQL which allows everything (you just have to use parenthesis to ensure proper evaluation):

SELECT col1, col2, col3
  FROM tbl
WHERE services IN ('FS', 'FL', 'IDA', 'REAP', 'SSHF', 'CD', 'DVRP')
   AND (FSenterdt1 between #1/1/2009# And #3/31/2009# 
        OR FSenterdt2 between #1/1/2009# And #3/31/2009#)

Open in new window

arthritisdogAuthor Commented:
Thank you for your help- I now understand!!!
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.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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