UDF in an IN statement not working properly in query

[CodeLiftSleep]

I am not sure exactly what is going wrong here...I have a created query that runs upon loading and I need to pass in different values based on who is logging in...

So I have a UDF that works fine when I am running it on a recordset, but does now work when I use it in the query...it returns nothing even though I check to make sure it is returning the proper values.



Basically I have the following:

WHERE ((t.JanSAM IN (strEmpArrVal())) with strEmpArrVal being the UDF returning a string of values which are formatted properly ('name1', 'name2', name3', etc...). Again, I am wondering why this works properly when I run an SQL statement against a recordset in code but not in the query with the same SQL statement.

Does anyone know why this is not working properly? Can I simply update the QueryDef to take the string value itself once I get it?

[June7]

The query object will see the return from the UDF as simply a string of characters (including apostrophes and commas), not as a set of discrete values. LIKE and wildcard might be able to work.

What is this query used for? If it is the RecordSource of a form or report, use the UDF to set Filter property. Or WHERE CONDITION of OpenForm/OpenReport.

[CodeLiftSleep]

Hmm...is it possible to feed the recordset to the query?

It's being used when a manager logs in, it will bring up his report, which shows a list of all his employees and their accounts they are responsible for...

the UDF returns his list of employees...the query is being used by the Report as the Source...

What are my options here?

[June7]

A query object like:

SELECT * FROM table WHERE ID IN (SELECT ID FROM datasource WHERE someCriteriaHere)

How do you 'know' these are his employees? Is there a "SupvervisorID" field in Employees table or Departments table or something somewhere can apply filter: =[enter supervisor ID]?

[CodeLiftSleep]

A query object like:

SELECT * FROM table WHERE ID IN (SELECT ID FROM datasource WHERE someCriteriaHere)

How do you 'know' these are his employees? Is there a "SupvervisorID" field in Employees table or Departments table or something somewhere can apply filter: =[enter supervisor ID]?

I get his user name via the Environ("usename") which gets checked against a table of managers---if he is found in there, I bring back his matching Id and set Manager privileges for the loggedUser. Then when I run the queries I check the employee table for any employee with a ManagerId of his(4 in this case) and bring back all the employees.

Then I go into the accounts table and bring back all matching accounts with those employees responsible for them(57 records in this case) matching the employee name to the array using IN for all 12 months as I need to bring back all accounts that at any point in the past 12 months were assigned to them....

I'm wondering I return all the accounts and then filter the report if that might be easier...can a filter accept an array of values?

[June7]

Can you build a query that includes accounts and employees tables? Apply ManagerID filter to the field from Employees table?

[CodeLiftSleep]

What I ended up doing was to create a temp table, download all of the accounts to that table and then run an INSERT INTO query to the main table and only put the accounts I needed in that table and then run the Report Query pulling back all records.