?
Solved

microsoft access 2010 iif criteria in query

Posted on 2014-03-17
2
Medium Priority
?
711 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 49

Accepted Solution

by:
Dale Fye earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

571 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