Need Criteria to equal nothing in IIF statement. (1 Viewer)

[Jmsteph]

I am trying to set up a query were it checks to see if the user is in management. If he/she is then the Criteria field needs to be be blank. If the user is not in Management then it will populate that Users ID so they can only view their records. Unfortunatly based on the below formula it is not pulling any records when the user is apart of management. I have tried Is Not Null, *, & "" but can't seem to figure it out. Any help is greatly appreciated. Thanks,

IIf(DLookUp("[Access_Level]","Auth","[NetID]='" & UserName() & "'")="Management",Is Not Null,"Like "*" & UserName() & "*"")

 

[HiTechCoach]

If this is in a query then try something like this:

Where [YourFieldNameField] Like IIf(DLookUp("[Access_Level]","Auth","[NetID]='" & UserName() & "'")="Management","*" , UserName() )

I have found that using a Dlookup() within a query will have a huge negative impact on performance. I would avoid them.

 

[irish634]

I am trying to set up a query were it checks to see if the user is in management. If he/she is then the Criteria field needs to be be blank. If the user is not in Management then it will populate that Users ID so they can only view their records. Unfortunatly based on the below formula it is not pulling any records when the user is apart of management. I have tried Is Not Null, *, & "" but can't seem to figure it out. Any help is greatly appreciated. Thanks,

IIf(DLookUp("[Access_Level]","Auth","[NetID]='" & UserName() & "'")="Management",Is Not Null,"Like "*" & UserName() & "*"")

I went through this EXACT same problem yesterday. Let me guess, you are putting the IIf statement in the criteria of a field on the query. I found a solution for it. It's ugly but it works.

Check out the thread I started for it.