Solved

microsoft access 2010 iif criteria in query

Posted on 2014-03-17
2
702 Views
Last Modified: 2014-03-17
I have a simple query:
SELECT MEMBERS.*
FROM MEMBERS

But, dependent on what the value of tempvars!user_level is, I want it to do one of these two actions:
if tempvars!user_level = 1 then it needs to do this:
 - "WHERE Province = tempvars!province"
if tempvars!user_level = 2 then it needs to do this:
 - "WHERE Province > 0

I've tried to construct this using IIF, but can't see how to do it. I have tried:

SELECT MEMBERS.*
FROM MEMBERS
IIf(tempvars!user_level = 1,(Province > 0,TempVars!province)
0
Comment
Question by:rick_danger
[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
  • Learn & ask questions
2 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39933830
SELECT MEMBERS.*
FROM MEMBERS
WHERE (tempvars!user_level = 1 AND Province = Tempvars!Province)
OR (tempvars!user_level = 2 AND Province > 0)
0
 

Author Closing Comment

by:rick_danger
ID: 39933859
Excellent solution. So simple - I don't know why I didn't think of that!

Thanks for a very clear solution.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

734 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