multiple criteria (Access 2002)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

It happens sometimes, I was just about to reply about using IN, before I noticed you had replied to your own post!

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.)

Reply | Quote

It happens sometimes, I was just about to reply about using IN, before I noticed you had replied to your own post!

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.)

Reply | Quote

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.

Reply | Quote