Output query to XLS using criteria created in form/VBA (1 Viewer)

RichO

Registered Yoozer
Local time
Today, 10:41
Joined
Jan 14, 2004
Messages
1,036
Hello,

I am not sure how to accomplish this. I am exporting a query to XLS, and this works by itself, however, the user selects the criteria from a list box in the form, so the query's criteria would be like:

In ("APPLES", "ORANGES", "BANANAS", "GRAPES")

Since there is no "WHERE" option for the OutputTo command, I am trying to figure out how to do this.

Any ideas would be appreciated.
 

Ranman256

Well-known member
Local time
Today, 11:41
Joined
Apr 9, 2015
Messages
4,339
if you want to pick various items to be in the criteria, use a tPicked table.
I have a listbox on a form, user dbl-clicks the item and an append query adds it to the tPicked table.
when picks are finished, click the Run button to open the query.
the query joines the main data table to the tPicked table.
this filters only those items.

pick state-lbl.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:41
Joined
Feb 19, 2002
Messages
42,970
In's are tricky since you can't pass them as a string to the query. So if he table solution wont' work for you, create the SQL to include the IN() and store it on your form in a hidden text box. Then the report when it opens can place the SQL from the text box into its RecordSource.

Me.RecordSource = Forms!yourform!HiddenSQL
 

RichO

Registered Yoozer
Local time
Today, 10:41
Joined
Jan 14, 2004
Messages
1,036
Thanks for the replies. If there was not an easier way, my plan was to create a temporary table using SQL from VBA and then export the table to XLS, I guess somewhat similar to the tPicked table idea.

create the SQL to include the IN() and store it on your form in a hidden text box.
I can see this working for a report but it was a query that I wanted to export to XLS so I wondering if there was a similar roundabout way to pass the VBA created "In" to the query.

Code:
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLS, "myFile.xls"
 

Cronk

Registered User.
Local time
Tomorrow, 02:41
Joined
Jul 4, 2013
Messages
2,770
There is an easier way. (I prefer not to use temporary tables because of potential for bloat.) Generate your sql and modify a query.

If you had a saved query, MyQuery and you are wanting to export fields F1,F2 and F4

str ="('APPLES', 'ORANGES', "BANANAS', 'GRAPES')"
set db = currentdb
set qdf = db.querydefs("MyQuery")
qdf.sql ="select F1,F2,F4 from yourTable where F1 in " & str
set qdf = nothing
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLS, "myFile.xls"
 

RichO

Registered Yoozer
Local time
Today, 10:41
Joined
Jan 14, 2004
Messages
1,036
That looks pretty easy. I will give it a try. Thanks
 

RichO

Registered Yoozer
Local time
Today, 10:41
Joined
Jan 14, 2004
Messages
1,036
So essentially, the queryDef permanently changes the query object each time it's run? It looks like that is what has happened.
 

Cronk

Registered User.
Local time
Tomorrow, 02:41
Joined
Jul 4, 2013
Messages
2,770
Yes, the query is modified (and saved). If you need the original query elsewhere either have two copies, one fixed and one changed. Or modify the query to remove the criteria in the same fashion.
 

NT100

Registered User.
Local time
Today, 23:41
Joined
Jul 29, 2017
Messages
148
There is an easier way. (I prefer not to use temporary tables because of potential for bloat.) Generate your sql and modify a query.

If you had a saved query, MyQuery and you are wanting to export fields F1,F2 and F4

str ="('APPLES', 'ORANGES', "BANANAS', 'GRAPES')"
set db = currentdb
set qdf = db.querydefs("MyQuery")
qdf.sql ="select F1,F2,F4 from yourTable where F1 in " & str
set qdf = nothing
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLS, "myFile.xls"

Can't catch up. Do you have an example for this?

Best.
 

Cronk

Registered User.
Local time
Tomorrow, 02:41
Joined
Jul 4, 2013
Messages
2,770
I would have thought the code snippet you quote was an example of the method I was suggesting.

What exactly is it that you are looking for?
 

RichO

Registered Yoozer
Local time
Today, 10:41
Joined
Jan 14, 2004
Messages
1,036
I was able to adapt it to my code and it worked perfectly.
 

Users who are viewing this thread

Top Bottom