Query that selects "all records" 1

[chris1603]

In an Employee database which goes across several subsidiary companies I want each Company User to be able to see ONLY their company records but Group Users should see ALL records.

The data source for my form is a query:

Select EmployeeID, EmployeeCompanyID etc from EMPLOYEES where EmployeeCompanyID = [Forms]![Login].[UserCompanyID]

This works for each individual company but the vital bit I need help with is that Group Users (UserCompanyID = 0) must be presented with all records. Any help would be very welcome.

Chris

 

[misscrf]

can you try (i'm not sure where it would go..)

=IIF ([UserCompanyID]=0,*,[UserCompanyID]

I would think in the criteria line of usercompanyid

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I wish I could edit that post - forgot end paren...

=IIF ([UserCompanyID]=0,*,[UserCompanyID])

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[chris1603]

Thanks misscrf,

It works where company <>0 but when it is 0 I get a message saying the whole thing is too complex to evaluate and I should try to add variables. I do have a lot of fields selected but this criteria is the only test and the data is not even sorted.

Any other ideas??

Chris

 

[misscrf]

have you tried -

=IIF ([UserCompanyID]=0,[UserCompanyID]= *,[UserCompanyID])

not sure on the true syntax, but something along that should work.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

Select EmployeeID, EmployeeCompanyID etc from EMPLOYEES where EmployeeCompanyID = [Forms]![Login].[UserCompanyID]
OR [Forms]![Login].[UserCompanyID] = 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chris1603]

PHV, You're a star. That works great. Thank you