Query Parameter Function

[Garrett Berneche]

Long story short I want to filter a query's results based on
CurrentUser and the user group they belong to; ie. if the CurrentUser
is not in the "Supervisors" group then they can see only their own
records. I have a working function that tells me if CurrentUser is in
a given user group (IsUserInGroup("Supervisors")), and if the
CurrentUser is not in the group the query behaves as I would like. My
problem is that if the the user is in the group I don't know how to
make the query return all records.

In the criteria for the query, under USER_NAME I have tried...
Iif(IsUserInGroup("Supervisors"), ,CurrentUser())
Iif(IsUserInGroup("Supervisors"), "",CurrentUser())
Iif(IsUserInGroup("Supervisors"), "*",CurrentUser())
Iif(IsUserInGroup("Supervisors"), *,CurrentUser())
Iif(IsUserInGroup("Supervisors"), like *,CurrentUser())
Iif(IsUserInGroup("Supervisors"), null,CurrentUser())
Iif(IsUserInGroup("Supervisors"), is null,CurrentUser())
Iif(IsUserInGroup("Supervisors"), is not null,CurrentUser())
Iif(IsUserInGroup("Supervisors"), true ,CurrentUser())
Iif(IsUserInGroup("Supervisors"), false,CurrentUser())
IIf(IsUserInGroup("Supervisors"),In (SELECT USER_NAME from
A_USERS),CurrentUser())

Any suggestions?

 

[Dorian]

What you posted is not a query, We really need to see your entire query.

The filtering will be done in a WHERE clause, something like
WHERE USER_NAME = IsUserInGroup(current-user-name) OR
IsUserInGroup(current-user-name) = '*'

This assumes your function returns the current user or '*' if a supervisor.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer]

A guess, but have you tried

LIKE IIF(IsUserInGroup("Supervisors"),"*",CurrentUser())

Or even the following if you are using ANSI-Compliant SQL
LIKE IIF(IsUserInGroup("Supervisors"),"%",CurrentUser())

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================