Exporting to text using access query with parameters

Question

Greetings,

I have an access query which uses built in parameters (e.g. [start date]) to prompt a user to enter a date parameter when the query is ran.

In most cases this has worked fine except today I tried to export the query to a text file and I am receiving an error:

Too few parameters. Expected 2.

This makes sense as there are two parameters in the query [start date] and [stop date], the reason it errors is that I am never prompted to supply a value.

If I export to Excel this works OK, just not to text files.

Any suggestions on how to get around this issue or allow me to export the query to a text file?

Thank you,

Brett

Example Access Query:

SELECT PR_EARN.Emp_No, PR_EARN.Pay_Code, PR_EARN.Hours, PR_EARN.Rate, PR_EARN.Pay_Amt, PR_EARN.Pay_Date
FROM PR_EARN
WHERE (((PR_EARN.Pay_Date) Between [Start Date] And [End Date]));

Answer 1

A couple more methods:

• There is a workaround given in the Microsoft's KB269671.
  Basically, you have to use an intermediary query with a special syntax.

• You can also change the query to a Make Table query and then export its data.

• Use and intermediary invisible datasheet form whose RecordSource is set to the query and then have it's FormLoad event export the form to text then close the form.
  Just opening the form would prompt the user to enter the parameters and then automatically save it.

Robert's answer may still be the simplest one though.

Answer 2

Create a new form called Export. Put two textboxes on the form, and name them StartDate and EndDate. Save the form. Change your query to read the following:

SELECT PR_EARN.Emp_No, PR_EARN.Pay_Code, PR_EARN.Hours, PR_EARN.Rate, PR_EARN.Pay_Amt, PR_EARN.Pay_Date
FROM PR_EARN
WHERE (((PR_EARN.Pay_Date) Between Forms!Export!StartDate And Forms!Export!EndDate));

Open the form by double-clicking it. Fill in the two date fields, and leave the form running. Export your query in the usual way.

Answer 3

I was able to get around this by using the eval function. So instead of:

Between [Forms]![Reporting Import and Export]![date_exportstart] And [Forms]![Reporting Import and Export]![date_exportend]

Use Eval:

Between Eval("[Forms]![Reporting Import and Export]![date_exportstart]") And Eval("[Forms]![Reporting Import and Export]![date_exportend]")