export expect based on a query

I need to export items from a table to a text file but I need to use a where clause to only export some based on a user selection (in this case only export records for a certain year)

This is what i am doing in the code

DoCmd.TransferText acExportDelim, "Export_File", "TableName",  "Filename.csv", True

i tried to replace the table with a sql statement that only selected certain items but it didn't let me.

How?
vbnetcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you have to create a query with your selected items from table  "TableName"
with all the criterias, save the query and use it instead of the  "TableName".

if the where clause as you indicated will be based on users selection,
you have to alter the SQL statement of the query using querydef in VBA codes.

give more information
0
vbnetcoderAuthor Commented:
>>if the where clause as you indicated will be based on users selection,
you have to alter the SQL statement of the query using querydef in VBA code

How can i pass variable to this query at run time so that i can have something like

where Year = @year (the value i passed in)
0
vbnetcoderAuthor Commented:
So i can do this

DoCmd.TransferText acExportDelim, "Export_File", "Mysavedquery",  "Filename.csv", True

but i have to pass a parameter to "mysavedquery"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
are you using a form for user selection of variables?

better upload a sample db with the form.

but basically here is the code to alter the sql statement of the query

dim qd as dao.querydef, db as dao.database, sWhere as string, ssql as string
set db=currentdb

set qd=db.querydefs("nameOfquery")

ssql="select col1,col2 from tablex"

swhere = " [Year]=" & me.comboboxYear

qd.sql=ssql & sWhere

docmd.openquery "nameOfquery"



DoCmd.TransferText acExportDelim, "Export_File", "nameOfquery",  "Filename.csv", True
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
I will give that a try.  The idea is correct i will test to see if i can get the syntax working now.
0
vbnetcoderAuthor Commented:
what is tablex? What is the relationship between that and the saved query "nameOfquery"
0
Rey Obrero (Capricorn1)Commented:
"tablex" is just a name of the table, change it with your table name.
0
vbnetcoderAuthor Commented:
Why do i need to reference a table name.  Shouldn't I be doing that in the saved query ""nameOfquery""?
0
PatHartmanCommented:
If you are using a form to run the export, place the argument on the form - either visible or hidden depending on how you want to use it.  Then the Where clause of the saved querydef will refer to the form field.

Where somefield = Forms!yourform!txtsomefield;
0
vbnetcoderAuthor Commented:
Pat that is what i did...

Rey, your code worked great.  The only wierd thing is that it actually opens up the query so all the data is shown to the user ... any way to avoid that?  They don't need to see rows and columns etc.
0
Rey Obrero (Capricorn1)Commented:
remove this line of code


 docmd.openquery "nameOfquery"
0
vbnetcoderAuthor Commented:
thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.