Use of IIF Function in Query Criteria Field (1 Viewer)

[Pac-Man]

Hello,

I've a query with only two fields from a table i.e. AccessLevelID and AccessLevel. I tried using this function in criteria but it do not work as expected/required:

IIF(GetAccessLevelID() = 1,/[AccessLevelID], [AccessLevelID] <> 1 And [AccessLevelID] <> 2)

Where GetAccessLevelID() is a function to acquire AccessLevelID of currently logged in user. What I want to achieve is if a user with AccessLevelID 1 is loved in, he could see all the users whereas other users can see all the users other than one with AccessLevelID equals to 1 and 2.

PS. I've already solved this issue using VBA setting different record source for different AccessLevelID through select case and SQL but I'm posting this question here out of curiosity and also I want to use IIF somewhere else.

Best Regards,
Abdullah

 

[theDBguy]

Hi. I don't think I really follow what you're trying to do. Can you please post the complete SQL statement to show how you tried to use the IIF() with a function in it? Thanks.

 

[Pac-Man]

Hi. I don't think I really follow what you're trying to do. Can you please post the complete SQL statement to show how you tried to use the IIF() with a function in it? Thanks.

Thanks for reply. I don't have PC right now as I'm on peace from office for free days. I just wanted to check if the use of this IIF statement is right. I may try ire-explain the question. I have a table tblAccessLevel with two fields only i.e. AccessLevelID and AccessLevel.

--------------------------------------------------
AccessLevelID AccessLevel
--------------------------------------------------
1. Developer
2. Administrator
3. Supervisor
4. Basic User

Logged in user can have any of the four AccessLevels. So GetAccessLevelID() returns value 1 to 4. I wish to see all users the SELECT query when a user with AccessLevelID 1 is logged in. And for any other user, they can see users with only AccessLevelID 3 and 4.

So in order to do this, I use SELECT query in design mode and added tblAccessLevel and added both the two fields of tblAccessLevel. And in the criteria row of AccessLevelID field of the query I added following expression:

IIF(GetAccessLevelID() = 1,[AccessLevelID], [AccessLevelID] <> 1 And [AccessLevelID] <> 2)

Now if user with AccessLevelID 1 is logged in, SELECT query works as desired ie it shows all the users but when some user with AccessLevelID <>1 is logged in, query show no user. So I'm curious to see if I made a mistake in IIF statement.

Best Regards

 

[arnelgp]

you can move the Expression to the Where Clause of your Query (SQL View):

Where IIF(GetAccessLevelID() = 1, True, [AccessLevelID] > 2)

 

[Pac-Man]

you can move the Expression to the Where Clause of your Query (SQL View):

Where IIF(GetAccessLevelID() = 1, True, [AccessLevelID] > 2)

Thanks for reply. Will it not be the same if I put it in criteria in design view? I'll try the expression you provided in SQL as well.