Solved

Query criteria from public function results

Posted on 2010-08-17
12
331 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 119

Expert Comment

by:Rey Obrero
ID: 33464153
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

947 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

19 Experts available now in Live!

Get 1:1 Help Now