Export Query to Text File

B

Big Dog

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

Big Dog

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
 
A

Allen Browne

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.
 
B

Big Dog

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
 
A

Allen Browne

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.
 
B

Big Dog

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.
 
A

Allen Browne

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.
 
B

Big Dog

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!
 
B

Big Dog

Allen:

are you available for another question associated with this topic? I have
an issue with some of the formatting of the data. Thanks.
 
A

Allen Browne

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")
 
B

Big Dog

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...
 
A

Allen Browne

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...
 
B

Big Dog

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...
 
M

Michel Walsh

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
 
Top