Solved

Query criteria from public function results

Posted on 2010-08-17
12
330 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:JoySloan
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Maybe:

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

mx
0
 

Author Comment

by:JoySloan
Comment Utility
Unfortunately that didn't work.  I get 'Run-time Error '13':  Type mismatch'
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:JoySloan
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
ok ... so the text 'or' is actually in the result ?

mx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Are you trying to return a 'string' of numeric OR conditions ?

mx
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:JoySloan
Comment Utility
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
 
LVL 58

Expert Comment

by:harfang
Comment Utility
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
 

Author Comment

by:JoySloan
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
0
 
LVL 58

Accepted Solution

by:
harfang earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:JoySloan
Comment Utility
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now