exporting a parameter query to fixed width text file

[spizotfl]

hi, i am looking to have a form where a user can select 1 of a few different queries from a combobox. i also want 2 textboxes for the user to enter a start date and end date to limit the query results. i would like to have a button that can take the data and export it to a fixed width text file.
i figure i would likely need to use DoCmd.TransferText. i can't figure out how to make things go from being on the form to the actual export operation. i need some help in figuring out how to get the text box values passed to the query and then exported.
any thoughts?

 

[PHV]

Use a parametized query, something like this:
PARAMETERS [Forms]![Name of open main form]![txtStartDate] DateTime, [Forms]![Name of open main form]![txtEndDate] DateTime;
SELECT ...
FROM ...
WHERE ([some date field] Between [Forms]![Name of open main form]![txtStartDate] And [Forms]![Name of open main form]![txtEndDate])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[spizotfl]

ah.... i remember that now.... thanks for the reminder. hopefully this will do the trick for now.
thanks,
mike

 

[spizotfl]

ok, i put the parameter info into the query and now i get a:

'qrySAOutcomes' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

which did not show up before hand.
Any thoughts?

 

[spizotfl]

this is a shortened version of the query, there are a few more lines in the select statement, but i can't get rid of that "Not a valid name" error. this occurs however i try to open the query.
i can view it in design mode and sql mode.

PARAMETERS [Forms]![frmStartDate]![StartDate] DateTime, [Forms]![frmStartDate]![EndDate] DateTime;
SELECT tblSAOutcomes.ContractorID, tblSAOutcomes.SiteID, tblSAOutcomes.SSN, tblSAOutcomes.ClientID, tblSAOutcomes.Residcoun, tblSAOutcomes.Grade, tblSAOutcomes.Marital
FROM tblSAOutcomes
WHERE (((tblSAOutcomes.EnterDate) Between [Forms!frmStartDate.StartDate] And [Forms!frmStartDate.EndDate]));

 

[PHV]

And what about this (in a brand new query SQL pane while frmStartDate is open and populated) ?
PARAMETERS [Forms]![frmStartDate]![StartDate] DateTime, [Forms]![frmStartDate]![EndDate] DateTime;
SELECT ContractorID, SiteID, SSN, ClientID, Residcoun, Grade, Marital
FROM tblSAOutcomes
WHERE EnterDate Between [Forms]![frmStartDate]![StartDate] And [Forms]![frmStartDate]![EndDate]

BTW, what is 'qrySAOutcomes' ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[spizotfl]

qrySAOutcomes is almost all of the data from tblSAOutcomes. it is stuff that needs to be uploaded to an agency as a fixed width text file.

 

[spizotfl]

i copied the sql into a brand new window and ran it. got the same error. maybe i am hitting up against the length part of the warning.... do you know what sort of length maximum there is for a query?

 

[PHV]

There is no reference to 'qrySAOutcomes' in the SQL code I gave you, so I wonder you get the same error ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[spizotfl]

well, the same error type, just with the new query name.

 

[spizotfl]

i pulled out the parameter part from the top and it worked. not sure why, would love to know....

 

[PHV]

Which new query name ?
Close all your apps.
Restart windows.
Open your db in access.
Launch the frmStartDate form and populate the StartDate and EndDate textboxes.
Create a brand new query without selecting any Query/Table.
Copy the sql code I gave you in the SQL view pane.
Run it.
What happens ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[spizotfl]

that worked fine. thanks