Export Query to Text File

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using Access 2003, is it possible to have a query create a text file with no
user intervention? Thanks
 
Allen:

thanks for the quick assistance, and I checked your webiste and it looks
like a valuable resource. can you point me to a good explanation of macro
creation/transfer text?

thanks
 
It's very simple:

1. Choose the Macros tab of the Database window.

2. Click New.
Access opens a new macro.

3. On the first row, choose the TransferText action.

4. In the lower pane, set:
Transfer Type Export Delimited
Spec {leave this blank}
Table Name {name of your table or query here}
File Name C:\MyFolder\MyFile.txt {or whatever}

5. Save the macro.

6. Set the On Click property of a command button to the name of your macro.
 
Allen:

thanks for the ample code - worked great. but I need to create a fixed
width text file, and when I make that checge I get an error message requiring
the Spec parameter. can you help, again? thanks
 
Pretend you are manually exporting the file.
While running the export wizard, click the Advanced button.
This takes you to a screen where you can save the specification.

You can now use that spec name in the macro.
 
Allen:

I'm sorry to keep bugging you, but I don't get the export wizard when I try
to export the query manually. Searched with Google for some help, no luck so
far. thanks again.
 
1. In the Database window, select the query.

2. Choose Export on the File menu.

3. In the file dialog, set the Save as Type drop-down to:
Text Files
Enter a file name, and click Export.

4. In the Export Text Wizard dialog, click the Advanced button.
 
Allen:

finally got it - I was running the query then trying to export it. my bad.
thanks for all of your EXTREMELY valuable help and time!
 
Allen:

are you available for another question associated with this topic? I have
an issue with some of the formatting of the data. Thanks.
 
Export a query.
You can format the data as desired.

For example, type an expression like this into the Field row:
Format([MyDate], "Long Date")
or perhaps:
Format([Amount], "Currency")
 
Allen:

My issue involves a dollar amount field that must be a fixed length with
leading zeroes and no character for the decimal point. ("000000043274" for
$432.74). The data is formatted OK in my query, but in the text file the
leading zeroes are lopped off and the physical decimal point is inserted
("432.74")

So it seems like it's happening during the export process, and I'm unable to
figure out how to define the field in the export wizard to get what I need.

thanks again
--
Bill Gable


Allen Browne said:
Export a query.
You can format the data as desired.

For example, type an expression like this into the Field row:
Format([MyDate], "Long Date")
or perhaps:
Format([Amount], "Currency")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Big Dog said:
Allen:

are you available for another question associated with this topic? I have
an issue with some of the formatting of the data. Thanks.
Click to expand...
Click to expand...
 
How about:
Format(100 * [Amount], "000000000000")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Big Dog said:
Allen:

My issue involves a dollar amount field that must be a fixed length with
leading zeroes and no character for the decimal point. ("000000043274" for
$432.74). The data is formatted OK in my query, but in the text file the
leading zeroes are lopped off and the physical decimal point is inserted
("432.74")

So it seems like it's happening during the export process, and I'm unable
to
figure out how to define the field in the export wizard to get what I
need.

thanks again
--
Bill Gable


Allen Browne said:
Export a query.
You can format the data as desired.

For example, type an expression like this into the Field row:
Format([MyDate], "Long Date")
or perhaps:
Format([Amount], "Currency")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Big Dog said:
Allen:

are you available for another question associated with this topic? I
have
an issue with some of the formatting of the data. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
will try. thanks

--
Bill Gable


Allen Browne said:
How about:
Format(100 * [Amount], "000000000000")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Big Dog said:
Allen:

My issue involves a dollar amount field that must be a fixed length with
leading zeroes and no character for the decimal point. ("000000043274" for
$432.74). The data is formatted OK in my query, but in the text file the
leading zeroes are lopped off and the physical decimal point is inserted
("432.74")

So it seems like it's happening during the export process, and I'm unable
to
figure out how to define the field in the export wizard to get what I
need.

thanks again
--
Bill Gable


Allen Browne said:
Export a query.
You can format the data as desired.

For example, type an expression like this into the Field row:
Format([MyDate], "Long Date")
or perhaps:
Format([Amount], "Currency")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

are you available for another question associated with this topic? I
have
an issue with some of the formatting of the data. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Gary,

Did you copy your files to a CD and then paste it back to a computer? Files
copied from a CD are pasted Read Only. Open Windows Explorer, right click on
the file, open Properties and uncheck Read Only under Attributes (at the
bottom of the General tab).
 
Maybe the file is read only (check with the Operating System). That may
happen if the file has been copied from a read only CD, as example. You may
have open the application in a read only mode too, either explicitly, either
it was already open in design mode when you opened it.


Vanderghast, Access MVP
 
Back
Top