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

x
?
Solved

microsoft access 2010 iif criteria in query

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

705 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