excel automation, cross tab query, parameterized queries

[Roger]

I've got a filter form to select customers, products, etc
and these are used in parameter queries, ie WHERE t_cuno Like
[Forms]![fmnuMain]![txtCust]

the parameter queries, roll up to a crosstab query, that is used to
create an excel worksheet
I use a queryDef structure to set the parameters, and that works fine

now, I've got to change the customer selection, to a multiple
selection, so the above changes to
WHERE t_cuno IN ( [Forms]![fmnuMain]![txtCust] )

which is invalid

I can use queryDef.sql to change the WHERE clause at excel creation
time, but that creates
db bloat

or I can call a function WHERE validCustno(t_cuno) = 1, which returns
1 is the custno matches
the user selection(s) but calling a function in a query really slows
it down

or I could create a temporary mdb in the user's myDocuments (multiple
users running on a
terminal server, so c:\temp won't work) which contains a customer
number selection table
with the selected IDs and then change my sub queries to join to this
table

what are other options ?

[Steve Jorgensen]

Since you are using automation to populate Excel, I'm assuming there's no
reason you have to change the SQL of a saved querydef to do what you want,
just use a temporary querydef ( .CreateQuerydef with "" as the Name argument),
or open the regordset using the SQL string directly.

Presto - no MDB bloat.

On 26 Jan 2005 11:38:39 -0800, le*********@natpro.com (Roger) wrote:

I've got a filter form to select customers, products, etc
and these are used in parameter queries, ie WHERE t_cuno Like
[Forms]![fmnuMain]![txtCust]

the parameter queries, roll up to a crosstab query, that is used to
create an excel worksheet
I use a queryDef structure to set the parameters, and that works fine

now, I've got to change the customer selection, to a multiple
selection, so the above changes to
WHERE t_cuno IN ( [Forms]![fmnuMain]![txtCust] )

which is invalid

I can use queryDef.sql to change the WHERE clause at excel creation
time, but that creates
db bloat

or I can call a function WHERE validCustno(t_cuno) = 1, which returns
1 is the custno matches
the user selection(s) but calling a function in a query really slows
it down

or I could create a temporary mdb in the user's myDocuments (multiple
users running on a
terminal server, so c:\temp won't work) which contains a customer
number selection table
with the selected IDs and then change my sub queries to join to this
table

what are other options ?