Filtering records using a function

[Guest]

Hello

I have a query that filters on StaffID based on the values returned by the
function getStaffList.

What i need to happen is that if the user is able to see ids 1, 2,5 then the
where cluase of the query become "Where staffID = 1 OR 2 OR 5"

the string "1 or 2 or 5' is returned by the function however its not working
because (i assume), the query is treating the full thing as a string.

is there any way to accomplish this? its a secured db so i cant just change
the query definition in code.

thanks.

 

[John Vinson]

Hello

I have a query that filters on StaffID based on the values returned by the
function getStaffList.

What i need to happen is that if the user is able to see ids 1, 2,5 then the
where cluase of the query become "Where staffID = 1 OR 2 OR 5"

the string "1 or 2 or 5' is returned by the function however its not working
because (i assume), the query is treating the full thing as a string.

is there any way to accomplish this? its a secured db so i cant just change
the query definition in code.

thanks.

Build the SQL in code, is the only easy way.

You could (at a heavy price in inefficiency) have your function return
a string like

^1^2^5^

and use a criterion

WHERE InStr(getStaffList([arguments]),"^" & StaffID & "^") > 0

John W. Vinson[MVP]

 

[Guest]

Thank you for that response.

I can't build the sql statment in code, because of user level security and
the users have NO permissions on the tables.

Your other method is the same as a co-worker had mentioned. It works great
(albeit with a performance issue)! Now all i need to do is code for circular
references created in that string.

Thanks again!

Hello

I have a query that filters on StaffID based on the values returned by the
function getStaffList.

What i need to happen is that if the user is able to see ids 1, 2,5 then the
where cluase of the query become "Where staffID = 1 OR 2 OR 5"

the string "1 or 2 or 5' is returned by the function however its not working
because (i assume), the query is treating the full thing as a string.

is there any way to accomplish this? its a secured db so i cant just change
the query definition in code.

thanks.

Build the SQL in code, is the only easy way.

You could (at a heavy price in inefficiency) have your function return
a string like

^1^2^5^

and use a criterion

WHERE InStr(getStaffList([arguments]),"^" & StaffID & "^") > 0

John W. Vinson[MVP]

 

[Bill (The Original)]

"Where staffID = 1 OR Where staffID = 2 OR Where staffID = 5"

 

[Michel Walsh]

Hi,


Make an inner join with a temporary table, one field, as many record as
values (no dup).


tempTable ' table name
f1 'field name
1
2
5 'values




SELECT a.*
FROM a INNER JOIN tempTable ON a.fieldName = tempTable.f1



will do. Since the tempTable is a table, you can have your user add and
remove data from it, without security problem, isn't it?



Hoping it may help,
Vanderghast, Access MVP