[AccessD] Using a query name in an in clause

I take it this is an Access query not a SQL query, since your message is on the AccessD forum. You could get around this problem by using static functions in the the subquery rather than references to the controls on the form. Also, presumably the subquery returns exactly one column.

Arthur 



----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Tuesday, March 6, 2007 8:18:45 AM
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