Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query criteria from public function results

Posted on 2010-08-17
12
Medium Priority
?
346 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
ID: 33461283
Maybe:

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

mx
0
 

Author Comment

by:JoySloan
ID: 33461699
Unfortunately that didn't work.  I get 'Run-time Error '13':  Type mismatch'
0
 
LVL 75
ID: 33461721
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:JoySloan
ID: 33461742
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
ID: 33461765
ok ... so the text 'or' is actually in the result ?

mx
0
 
LVL 75
ID: 33461769
Are you trying to return a 'string' of numeric OR conditions ?

mx
0
 

Author Comment

by:JoySloan
ID: 33461820
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
ID: 33463180
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
ID: 33463536
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 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 33464479
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
ID: 33464750
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

886 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