Solved

Query criteria from public function results

Posted on 2010-08-17
12
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

615 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