Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    89

    Unanswered: VBA filter based on other query?

    HI. I've got a form based on a very complicated stored procedure which I don't want to change. The primary field is ID.

    I want to filter this recordset based on another query. THe other query is just a list of all the IDs that match my criteria. So I want my form to only show the rows who's ID appears in the query. Currently I have

    Code:
     Me.Filter = "WHERE EXISTS (SELECT * FROM MyQuery WHERE ID=StoredProcedure.ID)"
    The error I'm getting is "Syntax error in Query expression". What's wrong with teh above filter?

  2. #2
    Join Date
    May 2009
    Posts
    258
    What happens if you try removing the "WHERE"?

    Ax

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the filter is the where expression minus the where word itself.
    Im not sure you can use the style of expression you want in th eway you want as part of a filter.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is this an MSSQL stored procedure? If so, you can't use the sproc as the source of a select statement...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2009
    Posts
    89
    The stored procedure is from an sql express server. Removing "wheer" didn't work.

    I'm using select for a query, not on the stored procedure. I've currently got one filter for example which shows just values from today, and the filter simply is "StoredProcedure.Date=Date()" and that works fine (becuase it filters after it received all teh data.

    All i want is to say "StoredProcedure.ID=(any ID present in the select statement)."

    Can anyone think of another way to do this? I don't want to make a seperate stored procedure just for this filter ebcause then I would have to reload the previous stored procedure and change the recordset.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I think you're misunderstanding what I meant...

    Stored procedures can not be present ANYWHERE in a SELECT statement. You can use a User Defined Function if you need to be able to return a dataset in that fashion, or you can spool up a table var/temp table and dump the results of a stored procedure to it then select from the variable instead of the sproc.

    invalid:
    SELECT * FROM mySproc
    or
    SELECT * FROM anything WHERE mySproc = anything

    valid:
    SELECT * FROM someTable INNER JOIN dbo.SomeUserDefinedFunction() f WHERE someTable.someField = f.someField
    or
    INSERT INTO #someTable EXEC mySproc
    SELECT * FROM #someTable
    Last edited by Teddy; 07-06-09 at 16:43.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2009
    Posts
    89
    ah, forgot to mention that the stored procedure is through a passthrough query, so from what I understand once the query has executed then I have a snapshot of the data and can filter it just as any other query (but can't change anything).

    and the form is based on this passthrough query (didn't realise there is another way to use stored procedures in accesS?).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •