Solved Query Iif to show all or equal ID (1 Viewer)

[brunomesquita]

Hi guys.

I'm in a corner here, I have tried several approaches but none worked.

I have a query based on my employee table and I want it to filter the employee list depending on two fields:

EmployeeTypeID: IIf([Forms]![frm2Switch]![txtUserType]=2,([tblEmployees].[EmployeeTypeID])>=2,IIf([Forms]![frm2Switch]![txtUserType]=3,([tblEmployees].[EmployeeTypeID])>3,4)) - this is working by the way.

EmployeeManager: here is where I'm having problems. I want the query to check the txtUserType on the form and if it is equal to 2 (manager type), then show all employees on the list, else (if 3, supervisor) use the txtUserID as the EmployeeManager filter.

Did I explain this right?!

First I want my query to check if txtUserType is a manager type [2] if so then show all employee types [2, 3 or4].
Second I want the query to check if txtUserType is a manager [2] if so then show all employees else, filter the result using the txtUserID as the [EmployeeManager] filter.

There, now I think it is clear.

Any pointers would be much appreciated!

Cheers,

Bruno

 

[arnelgp]

Add Criteria to EmployeeTypeID field.

IIf([Forms]![frm2Switch]![txtUserType]=2, [EmployeeTypeID] , [Forms]![frm2Switch]![txtUserType])

 

[brunomesquita]

Add Criteria to EmployeeTypeID field.

IIf([Forms]![frm2Switch]![txtUserType]=2, [EmployeeTypeID] , [Forms]![frm2Switch]![txtUserType])

Hey arnelgp, thanks, but I got the EmployeeTypeID working fine.

My problem is when I try to enter the criteria in the EmployeeManager field (I'm trying to add different criteria to different fields).

Cheers!

 

[arnelgp]

try substituting the EmployeeManager to the Criteria i post and use it as criteria of EmployeeManager field

IIf([Forms]![frm2Switch]![txtUserType]=2, [EmployeeManager] , [Forms]![frm2Switch]![txtUserType])

 

[brunomesquita]

try substituting the EmployeeManager to the Criteria i post and use it as criteria of EmployeeManager field

IIf([Forms]![frm2Switch]![txtUserType]=2, [EmployeeManager] , [Forms]![frm2Switch]![txtUserType])

Hey arnelgp, I tried your suggestion, but it didn't work; the query returns empty.

 

[brunomesquita]

I figured out the logic.

If [EmployeeTypeID] = 2, >1, If [EmployeeTypeID] = 3 , >2, 4 --> this will verify is the user is a manager [2] or supervisoor [3], then show the respective records.

The same approach should work for [EmployeeManager].

Now, this is awkward... I changed the location of the reference field to the login form that remains open but hidden.

If I type the values (>1 and >2), the query behaves just fine, showing the records I want to show.

But when I place the formula on the criteria field, the query returns empty. I've checked the form Login, and the value is populated there, and if I use the [Forms]![frm1Login]![txtUserType] as a criteria in the [EmployeeType] it shows the right records. Why is my query returning empty?

 

[arnelgp]

Can you upload a sample db.

 

[brunomesquita]

Can you upload a sample db.

I told you this was awkward... and now I feel embarrassed.

I got it working with your suggestion @arnelgp from post #2, thank you very much.

Cheers,

Bruno.