Pass User Input as Parameter to DoCmd.OutputTo?

[zoroaster]

This may not be possible, but here goes...

I have a form from which I want to output the results of a number of queries into Excel spreadsheets. But I want the queries to run from one parameter (an 'earliest date') which is input into a textbox on the form. I've managed to get it all working hunky dorey as a number of parameter queries, but this involves the user typing in the same date six times! Is it possible to use the DoCmd.OutputTo method to output from the queries whilst adding an SQL "where" clause which acts as a parameter? Here's a snippet of code so far:

DoCmd.OutputTo acOutputQuery, "DEPT_LEAVERS_OUTPUT", acFormatXLS, "C:\WINNT\DEPT_LEAVERS_OUTPUT.xls"
DoCmd.OutputTo acOutputQuery, "DEPT_STARTERS_OUTPUT", acFormatXLS, "C:\WINNT\DEPT_STARTERS_OUTPUT.xls"

etc.

I'd like to somehow add

"WHERE Start_D >=" & Me.TxtDate

to the statements... I'm just not sure if I'm having a syntactical problem, or I need to go another way around it - maybe change the queries to Make Table queries, pass the textbox value to them, then output the resulting tables to Excel?

Any thoughts?






42??? We're going to get lynched!

 

[randy700]

Why not just put the 'where' criteria in each query?

 

[KenReay]

Hi

To expand on Randy's comment, if you set the criteria to Forms!MyForm!TxtDate where MyForm is the name of your form, then all should work as you want

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[og1975ze]

It sounds like all you need to do is within each of your queries you are wanting to export, you just need to make the criteria on the date range equal your Textbox from the form. If you do that you can just create your SQL statements that say do.cmd transferspreadsheet bla bla bla. The execution of the queries is all dependant on the data being passed from the form(textbox). Your query crieteria might look like this:
for Query "DEPT_STARTERS_OUTPUT"

field: Date
table: tbl_DEPT_STARTERS
crieteria: Forms!frm_main!txtDate

 

[zoroaster]

Can you do this in the SQL statement? I usually design queries in the design grid, and have struggled in the past to add references to form controls there...
I'll give it a bash - thanks!

42??? We're going to get lynched!

 

[zoroaster]

Sorry folks, it's not working - I tried the query with reference to the textbox (with '01/01/04' entered in it) and also with the date entered into the criteria as #01/01/04#, and the latter pulled back records, but the former didn't - what's the exact syntax in the SQL statement? - here's (abridged) WHERE statement I tried:

WHERE ((POS_HIST.START_D)>=[Forms]![FrmMain]![TxtDate]) ORDER BY etc..

Help!

42??? We're going to get lynched!

 

[zoroaster]

Sorry! Forget it, it's my fault - I mistyped! It's now working just peachy!

Many thanks to all concerned!!!

42??? We're going to get lynched!