Query criteria from public function results

I am trying to take the results of my function and use it as the criteria for a query, but i'm having no luck.  I'm thinking there's a problem with the data type, as this function results in a string, while the field that i'm using this result for is numeric.  If I show a msgbox with Allowed(), I get  '1 or 2 or 3' which would work fine if i typed that into the query criteria, but using 'Allowed()' as the query criteria gives me no records. Please help.

Here's the function:

Public Function Allowed()

Dim qdf As QueryDef
Dim rst As DAO.Recordset

    Set qdf = CurrentDb.QueryDefs!CoStaffQuery
    Set rst = qdf.OpenRecordset

    Do Until rst.EOF
        allowed1 = allowed1 & rst("companyid") & " or "
        rst.MoveNext
    Loop
   
Allowed = Left(allowed1, Len(allowed1) - 4)

End Function
JoySloanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Maybe:

Allowed = CLng( Left(allowed1, Len(allowed1) - 4) )

mx
0
JoySloanAuthor Commented:
Unfortunately that didn't work.  I get 'Run-time Error '13':  Type mismatch'
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
That doesn't make sense ... if Allowed is returning what can be construed as Numeric ...

Does the result contain anything else ... any special characters?  

Can you upload the db ?

mx
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JoySloanAuthor Commented:
The db contains proprietary info, so unfortunately, I can't upload it.  This image provides the result of the Allowed function in a message box.  There are no special characters.
Allowed-Message-Box.jpg
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... so the text 'or' is actually in the result ?

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are you trying to return a 'string' of numeric OR conditions ?

mx
0
JoySloanAuthor Commented:
Yes, the text 'or' is in the result.

I want to programatically put those numeric OR conditions into the criteria line of the query
Query-Image.jpg
0
harfangCommented:
You are trying to use the query grid to build a dynamic query. This doesn't work. You could do that through VB, for example:

    strSQL = "... your query ..."
    strSQL = strSQL & " WHERE Company In(" & Allowed() & ")"

Allowed() must return a comma-separated string, e.g.: "1,2,3".

But the real solution is to use the database engine to solve your problem. In the query grid, add the query CoStaffQuery, and link using the field Company (to CompanyID of the query). The link will restrict the output to only those records where the company ID is found in your query. If the numbers are not unique, make sure not to select any field from CoStaffQuery, and set the option “unique records” in the query properties.

Using the database engine for this type of tasks is the most natural. If you really need to build a dynamic query, you cannot use the query design grid, but you need to build the entire query from code.

Cheers!
(°v°)
0
JoySloanAuthor Commented:
Hi harfang, I'm excited and I think you're on the right track with adding the CoStaffQuery to the guery grid, though it's still not working.  Here's some background:

The CoStaffQuery is pulling information from a table that exists in a many-to-many relationship between companies and staff, meaning a staff member (StaffID) can have access to more than one company, and each company (CompanyID) can have more then one staff member.  So the resulting table is CoStaff with two primary keys, StaffID and CompanyID.  From the CoStaff table, I created the CoStaffQuery that pulls the StaffID and (multiple) CompanyIDs for only the current user.  What I am ultimately trying to do is limit the companies (Opportunity.Company) to only those allowed to the user.

Hope this makes sense, and thanks again!
0
harfangCommented:
Based on your code above, please try the following criteria, replacing allowed():

    In (Select companyid From CoStaffQuery)

If your function would have worked (logically, not syntactically), this should do what you were trying to do. If it does, you can try the linked table approach, with is sometimes more efficient.

(°v°)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoySloanAuthor Commented:
harfang, the  - In (Select CompanyID From CoStaffQuery) -  worked perfectly!!  You are fantastic!!

Not sure why the linked table didn't work, because it sounds like it should have.  Using it gave me a Recordset not updateable error.  Anyways, thanks a million.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.