[AccessD] Using a query name in an in clause

IT needs to be: WHERE AgencyID IN (SELECT AgencyID FROM
qryAgencyIDResultSet)

Hth
Steve


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Tuesday, March 06, 2007 8:19 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Using a query name in an in clause

I am trying to set up a filtered combo.  The combo needs to display records
"where AgencyID in (qryAgencyIDResultSet)" so to speak.  If I replace the
name qryAgencyIDResultSet with the sql that makes up qryAgencyIDResultSet
the query works just fine.  I really need to use a query name as shown
initially for the simple reason that the query references controls on forms
/ fltr() type things in order to select that result set.  Doing this
directly in the subquery gets darned messy.
 
Is it possible (is there any syntax) to reference the name of a query as the
subquery in an IN clause, rather than specifically placing the subquery's
SQL statement inside of the IN clause?  
 
The only way I have accomplished this in the past was to dynamically build
the query in a string variable and then assign that to either the combo's
property directly, or by editing the query def sql property.  I am trying to
avoid that messy stuff.
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com