Export Results of Access 2000 query to text file.

[Guest]

Greetings all - I am in need of assistance and appreciate any guidance!

Two issues with one export file...

1) I am attempting to export data from Access 2000 query (data tables are
linked to SQL tables) - Export needs to be one .txt file, consisting of
detail records and one totals record. The query creating the totals record
includes a 'count' field, whose result must be right justified and zero
filled - how do I format the result of a counted field?

2) To export, I used the export wizard to create an export specification
file. I then use TransferText and this specification file. One of fields
being exported was originally a decimal. In the exported query, I have
converted the number to a string and removed the decimal, then right
justified and zero filled. This appears to work (in datasheet view of
query). When I run the export wizard and create specification file the
display in the wizard shows correctly. However, the exported text file has
added the decimal and two 00 after the field - any ideas why and how do I get
rid of it?


Thanks in advance for any assistance!!

 

[John Nurick]

Greetings all - I am in need of assistance and appreciate any guidance!

Two issues with one export file...

1) I am attempting to export data from Access 2000 query (data tables are
linked to SQL tables) - Export needs to be one .txt file, consisting of
detail records and one totals record. The query creating the totals record
includes a 'count' field, whose result must be right justified and zero
filled - how do I format the result of a counted field?

Same as any other field: e.g. this will right-justify the number in a
field padded with zeroes:

Format(COUNT([MyField]), "0000000000")

2) To export, I used the export wizard to create an export specification
file. I then use TransferText and this specification file. One of fields
being exported was originally a decimal. In the exported query, I have
converted the number to a string and removed the decimal, then right
justified and zero filled. This appears to work (in datasheet view of
query). When I run the export wizard and create specification file the
display in the wizard shows correctly. However, the exported text file has
added the decimal and two 00 after the field - any ideas why and how do I get
rid of it?

It sounds as if you just need to use the Format() function as above.

To right-justify and pad with spaces rather than zeroes, use something
like this:

Right(" " & Format([MyField], "0"), 10)

 

[Guest]

Thank you John, your answer helped me realize that I needed to correct the
problem in SQL view - the design view wouldn't let me format in the Total row.

Thanks again!

Greetings all - I am in need of assistance and appreciate any guidance!

Two issues with one export file...

1) I am attempting to export data from Access 2000 query (data tables are
linked to SQL tables) - Export needs to be one .txt file, consisting of
detail records and one totals record. The query creating the totals record
includes a 'count' field, whose result must be right justified and zero
filled - how do I format the result of a counted field?

Same as any other field: e.g. this will right-justify the number in a
field padded with zeroes:

Format(COUNT([MyField]), "0000000000")

2) To export, I used the export wizard to create an export specification
file. I then use TransferText and this specification file. One of fields
being exported was originally a decimal. In the exported query, I have
converted the number to a string and removed the decimal, then right
justified and zero filled. This appears to work (in datasheet view of
query). When I run the export wizard and create specification file the
display in the wizard shows correctly. However, the exported text file has
added the decimal and two 00 after the field - any ideas why and how do I get
rid of it?

It sounds as if you just need to use the Format() function as above.

To right-justify and pad with spaces rather than zeroes, use something
like this:

Right(" " & Format([MyField], "0"), 10)

Thanks in advance for any assistance!!