Export query with passed criteria

[MarMo]

Hi ,
I'm trying to figure out what is the best way to pass criteria to a
query and then export this query.
I'm looping through a query to get the criteria and the result is put
into the strSQLExtrCLNR variable.
strSQLExtrCLNR = strSQLExtrCLNR & "," & ![ExtrClientNr]
The outcome could be : "22000,35000,36000,36001"
I use the following string to query (WHERE .... IN("...."):
strSQLInvoices = "SELECT * FROM QryInvoiceOrders WHERE [ClientNr] IN("
& strSQLExtrCLNR & ")"
How can i export this query , because i tried to open this in a
openquery statement but i get an error.
Is it better to use the [Clientnr]=22000 OR [ClientNr]= 35000 .......
and then export it.

Thanks for helping me.
Mario

 

[Allen Browne]

Save a query to use for export, and then assign your SQL string to the SQL
property of the QueryDef.

This kind of thing:
strSql = "SELECT ...
CurrentDb.QueryDefs("qry2Export").SQL = strSql
DoCmd.TransferText acExportDelim, , "qry2Export", strFile

 

[MarMo]

Hi Allen ,
Visited your site quite often.
I'm not familiar with QueryDefs and how and when to use them.
Perhaps i'll need to read about these things.
I'll try this out and let you know.

Mario

 

[MarMo]

Hi Allen ,
I tried this with the QueryDef as follows :
Works fine after the first loop.
Set dbInvoices = CurrentDb()
Set rstInvoices = dbInvoices.OpenRecordset(strSQLInvoices,
dbOpenDynaset)
Set qdfInvoices = dbInvoices.CreateQueryDef("qry2Export")
CurrentDb.QueryDefs("qry2Export").SQL = strSQLInvoices
DoCmd.TransferText acExportDelim, "specExportOrders", "qry2Export", "C:
\" & strCLNM & ".txt", True

This is within a 3 nested While .....Wend and this code is in the 3rd
While.
1st While loops through the clients to get the clientname and number.
2nd While (within the first) loops through the emailaddresses for this
client.
3rd While (within the second) loops through the specified criteria per
client.
So if the code loops the first time it creates the QueryDef and
assigns the strSQLInvoices string.
Exporting works fine.
But when the code loops for the second time , i get an error that the
QueryDef already exists.
I can only assign the criteria for the querydef when the
strSQLInvoices has been created.
How can i solve this - by deleting the QueryDef after exporting , or
is there another solution.

 

[Krzysztof Naworyta]

MarMo wrote:

| I tried this with the QueryDef as follows :
| Works fine after the first loop.
| Set dbInvoices = CurrentDb()
| Set rstInvoices = dbInvoices.OpenRecordset(strSQLInvoices,
| dbOpenDynaset)
| Set qdfInvoices = dbInvoices.CreateQueryDef("qry2Export")
| CurrentDb.QueryDefs("qry2Export").SQL = strSQLInvoices
| DoCmd.TransferText acExportDelim, "specExportOrders", "qry2Export",
| "C: \" & strCLNM & ".txt", True
|
| This is within a 3 nested While .....Wend and this code is in the 3rd
| While.
| 1st While loops through the clients to get the clientname and number.
| 2nd While (within the first) loops through the emailaddresses for this
| client.
| 3rd While (within the second) loops through the specified criteria per
| client.
| So if the code loops the first time it creates the QueryDef and
| assigns the strSQLInvoices string.
| Exporting works fine.
| But when the code loops for the second time , i get an error that the
| QueryDef already exists.
| I can only assign the criteria for the querydef when the
| strSQLInvoices has been created.
| How can i solve this - by deleting the QueryDef after exporting , or
| is there another solution.

1. Never use CurrentDb() function more then once in your procedure.
Use variable instead

2. Open/Create QueryDef object before starting your loops.

Sub test1

Dim db as database
Dim rs as recordset
Dim qr as querydef

set db = Currentdb
Set rs = db.OpenRecordset(...)

'ensure query exists or not

On error resume next
set qr = db.QueryDefs("qry2Export")
if err<>0 then
set qr = db.CreateQueryDef("qry2Export")
err.clear
end if
On error goto err_exit

While ...
While ...
While ...
qr.SQL = "select ...."
DoCmd.TransferText acExportDelim, "specExportOrders", "qry2Export",...
Wend
Wend
Wend

(...)
exit sub

err_exit:
(...)
End Sub

 

[Allen Browne]

You could create a query from scratch like that, though the OpenRecordset is
not required. Here's how:
http://allenbrowne.com/func-DAO.html#CreateQueryDAO

But I find it simpler to just manually create a query in each application
for this purpose. It really doesn't matter what is says initially: you just
assign its SQL property as shown in the previous reply.

 

[MarMo]

Hello ,

Thanks for the advice on the use of Currentdb().
I thought until now that i always needed to create a db as
DAO.Database for each recordset.
I changed my code to 1 db = Curentdb and it works fine.
The same for the QueryDef , i created it each time i needed to pass
the criteria.
So i set it before looping. It works fine.

You're great and thanks for helping me.
I'll probably be back with other questions.