By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,284 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,284 IT Pros & Developers. It's quick & easy.

excel automation, cross tab query, parameterized queries

P: n/a
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 ?
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 ?


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.