artful at rogers.com
artful at rogers.com
Tue Mar 6 08:42:41 CST 2007
You are incorrect about when the static needs to be evaluated, JC. When creating your subquery, place the name of the static function in the criteria row of the appropriate column in the builder. That's all you need to do. Of course, you must set the static function, prior to running the query, from your front end, but if that is done then the query will run exactly as expected. I have lots of queries and subqueries that work just like this. The only down-side is that should you ever have to migrate the query to SQL Server, then you have to rewrite it as a UDF that takes parameters. Anyway, I thought that you wanted to use the static inside the subquery, not within the IN() clause. Perhaps I misread the original message. 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:55:58 AM Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com