Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049

    Unanswered: Simple Subquery Fails?

    Hiya.

    I have a subquery SQL in VBA that translates to this:

    Code:
    SELECT * FROM [R_MemberReporting] 
    WHERE MemberID In(SELECT MemberID FROM [R_ActivityReporting] WHERE ActivityDate Between #22 Sep 2008# And #22 Oct 2008# )
    R_MemberReporting and R_ActivityReporting are both queries that return similar results, but the activity one has many records for a member. My mission is to filter the R_ActivityReporting, then get only those records from R_MemberReporting that are result.

    Sounded simple to me.

    I can even get that SQL, stick it in a query and run it perfectly.

    But when I try to open a report using this exact same SQL, I get this message and I don't understand what I am missing:

    3306 - You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's from clause. Revise the SELECT statement of the subquery to request only one field.
    Now that sounds logical, but the subquery

    Code:
    SELECT MemberID FROM [R_ActivityReporting] WHERE ActivityDate Between #22 Sep 2008# And #22 Oct 2008#
    Does only return one field.

    I tried first joining the two queries together with a link and showing only DISTINCT fields from R_MemberReporting, but that didn't work because I need to apply a filter to the R_ActivityReporting first ... as you can see in the subquery.

    Any ideas what I'm missing?

    Here's the VBA that generates the SQL (based on report dialog settings):

    Code:
    vSQL = "SELECT * FROM [R_MemberReporting] WHERE MemberID In(SELECT MemberID FROM [R_ActivityReporting] WHERE " & vMainCriteria & " )"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Try not to throw an entire SQL statement at the report you moron! Serves you right for working too late!!

    Next time, just get the criteria from the SQL (the WHERE clause without the word WHERE) and use that to open the report!

    All fixed!
    Last edited by StarTrekker; 10-22-08 at 10:00.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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