Solved

microsoft access 2010 iif criteria in query

Posted on 2014-03-17
2
696 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
2 Comments
 
LVL 47

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now