Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple criteria (Access 2002)

    Thanks for all the help I recieved in the past. I have another question:

    Is it possible to pass the results from a subquery that returns a single field but multiple rows to be used as the criteria for another query. I created a subquery then transfered the SQL code to the appropriate criteria line in a query grid. I received an error message stating "At most, one record can be returned by this subquery". Is there a way to force the results of a subquery to return as an array?

    Just a little detail: I'm writing a query to tally data from several different "Group Numbers" The query returns just one record with all groups totaled. The problem is that sometimes this report will be run for just one group, other times it will be run for 15 or more groups totaled together. I can manually type in the criteria and the query runs fine, but can't seem to figure out how to get it from a table. I need to save all the group numbers necessary for each report, since they will be run quarterly. The group numbers are 18 characters in length and I need to reduce typing errors. I have written a routine to save the numbers to a table, but then using them later as criteria for a query has proven to be problematic. Anyone have any ideas for this one?

    Again, thanks for your help.

  2. #2
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple criteria (Access 2002)

    I think I answered my own question: When I placed the SQL code into the criteria row, it needed to be wrapped by parentheses. Adding the keyword IN in from on the Select statement before the parentheses solved the problem. I guess I just needed to talk out loud.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple criteria (Access 2002)

    It happens sometimes, I was just about to reply about using IN, before I noticed you had replied to your own post! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    When you use a subquery, you must think of it as a 'variable'. If the query returns only one field, and one record, then it fits the variable profile. But if it returns multiple records, then it is like an array, instead of a normal variable. In that case, In can be used, since IN is a statement that allows you to provide an 'array' of values to check. (In (1,3,5,6), etc.)

Posting Permissions

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