[AccessD] Using a query name in an in clause

Arthur,


>Also, presumably the subquery returns exactly one column.
yes

>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 subquery rather than references to the controls on the
form. Also, presumably the subquery returns exactly one column.

As you must know by now, you and I are the (co) kings of static functions.
And unfortunately, a static function doesn't help, for the reason that it
has to be evaluated before it can be inserted in the SQL statement.  I am
trying to make a simple query that sits in the combo that is being filtered.
The IN clause of a SQL statement appears to accept ONLY a comma delimited
list, or a query that returns a list.  It (apparently) cannot contain a
function, a name of a query etc.

I am quite capable of dynamically building a SQL statement and assigning
that sql string to the property of the table but I wanted to avoid that.  

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Tuesday, March 06, 2007 8:40 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com