Export Results of Access 2000 query to text file.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kipper - 27 Nov 2006 16:23 GMT

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

Reply to this Message

John Nurick - 27 Nov 2006 21:03 GMT

>Greetings all - I am in need of assistance and appreciate any guidance!
>
[quoted text clipped - 5 lines]
>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
[quoted text clipped - 5 lines]
>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!!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

kipper - 28 Nov 2006 00:03 GMT

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!
> >
[quoted text clipped - 34 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message