By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Export fixed-width file

P: n/a
Hi and TIA! I have a query that I want to save as a fixed-width text file. I can get the export to
work using an export specification with one exception. This is a government message and they need
the remarks field to print on a separate line from the details section and I can' figure out how to
get the remarks to start on a new line. So what I want is (Start Column & Width in parenthesis)

Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)

Is this possible? I've looked through all my literature and the NG, but have yet to come up with a
solution. Any advice or pointers as to where I can look up more info is appreciated. As always,
Thanks for your time!

--
Reggie

----------
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
You could append a new line character(s) in front of the remarks field - in
your query, change the field to a calculated field such as:

tRemarks: Chr(13) & Chr(10) & [Remarks]

Since Access will count the two characters as part of the field, you'll need
to extend the field size in the specification to 52 characters though.

--
Shane Suebsahakarn
----
Head of IT
PAN Telecom
Tel: +44 (0) 870 757 7001

"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:k4********************@comcast.com...
Hi and TIA! I have a query that I want to save as a fixed-width text file. I can get the export to work using an export specification with one exception. This is a government message and they need the remarks field to print on a separate line from the details section and I can' figure out how to get the remarks to start on a new line. So what I want is (Start Column & Width in parenthesis)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)

Is this possible? I've looked through all my literature and the NG, but have yet to come up with a solution. Any advice or pointers as to where I can look up more info is appreciated. As always, Thanks for your time!

--
Reggie

----------

Nov 13 '05 #2

P: n/a
Hi Reggie

I think this will work for you...

************************************************** *********
Private Sub cmdExportQuery_Click()

Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rst As DAO.Recordset
Set rst = MyDB.OpenRecordset("qryReggie", dbOpenDynaset)

Open "C:\TEMP\TESTFILE.txt" For Output As #1 ' Open file for output.
'Note: The directory that will contain the text file (in this case
"C:\TEMP") must exist.

With rst
.MoveLast 'Populate the recordset
.MoveFirst
Do Until .EOF
'Print the contents of the first set of fields on the first line
Print #1, !Nomen; Tab(22); !PartNo; Tab(36); !TackNo

'Print the contents of the Remarks field on the second line
Print #1, !Remarks

Print #1, 'Print a third (blank) line to delineate the record

If Not .EOF Then .MoveNext
Loop
Close #1 'Close the text file.
.Close 'Close the recordset
End With

Set rst = Nothing
Set MyDB = Nothing

End Sub
************************************************** *********

--
--
HTH,
Don
=============================
E-Mail (if you must) My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================
I was "anally raped" on a Timeshare deal by Club All Seasons
http://worf.usshurdman.com/~calgary/...id=26180&code=
, Les Volieres du Quebec, and Club Privilege.

In appreciation for that, please feel free to forward SPAM and VIRUSES to:
ch**********@bellnet.ca
ip*****@hotmail.com
in**@volieres.com

Don't get mad --- get even! :-)
================================================== ========================

"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:k4********************@comcast.com...
Hi and TIA! I have a query that I want to save as a fixed-width text file. I can get the export to work using an export specification with one exception. This is a government message and they need the remarks field to print on a separate line from the details section and I can' figure out how to get the remarks to start on a new line. So what I want is (Start Column & Width in parenthesis)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)

Is this possible? I've looked through all my literature and the NG, but have yet to come up with a solution. Any advice or pointers as to where I can look up more info is appreciated. As always, Thanks for your time!

--
Reggie

----------

Nov 13 '05 #3

P: n/a
cool! I like that solution and will have to remember that trick.

"Shane Suebsahakarn" <sh***@REMOVETHISgcicom.net> wrote in message
news:cg**********@titan.btinternet.com...
You could append a new line character(s) in front of the remarks field - in your query, change the field to a calculated field such as:

tRemarks: Chr(13) & Chr(10) & [Remarks]

Since Access will count the two characters as part of the field, you'll need to extend the field size in the specification to 52 characters though.

--
Shane Suebsahakarn
----
Head of IT
PAN Telecom
Tel: +44 (0) 870 757 7001

"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:k4********************@comcast.com...
Hi and TIA! I have a query that I want to save as a fixed-width text file. I can get the export to
work using an export specification with one exception. This is a

government message and they need
the remarks field to print on a separate line from the details section

and I can' figure out how to
get the remarks to start on a new line. So what I want is (Start Column
& Width in parenthesis)

Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)

Is this possible? I've looked through all my literature and the NG, but

have yet to come up with a
solution. Any advice or pointers as to where I can look up more info is

appreciated. As always,
Thanks for your time!

--
Reggie

----------


Nov 13 '05 #4

P: n/a
Shane, Works like a champ. Pretty cool. I just couldn't figure out how to get that CrLf
incorporated into the Spec. Now I know. Appreciate the help. Been kick'n my butt for a few days
now. Take care!

--
Reggie

----------
"Shane Suebsahakarn" <sh***@REMOVETHISgcicom.net> wrote in message
news:cg**********@titan.btinternet.com...
You could append a new line character(s) in front of the remarks field - in
your query, change the field to a calculated field such as:

tRemarks: Chr(13) & Chr(10) & [Remarks]

Since Access will count the two characters as part of the field, you'll need
to extend the field size in the specification to 52 characters though.

--
Shane Suebsahakarn
----
Head of IT
PAN Telecom
Tel: +44 (0) 870 757 7001

"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:k4********************@comcast.com...
Hi and TIA! I have a query that I want to save as a fixed-width text

file. I can get the export to
work using an export specification with one exception. This is a

government message and they need
the remarks field to print on a separate line from the details section and

I can' figure out how to
get the remarks to start on a new line. So what I want is (Start Column &

Width in parenthesis)

Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)

Is this possible? I've looked through all my literature and the NG, but

have yet to come up with a
solution. Any advice or pointers as to where I can look up more info is

appreciated. As always,
Thanks for your time!

--
Reggie

----------


Nov 13 '05 #5

P: n/a
Don, This works like a champ also just like Shane's advice above does. So many choices now I don't
know what to do with myself. Thanks much for your time!

--
Reggie

----------
"Don Leverton" <le****************@telusplanet.net> wrote in message
news:u0SWc.5605$A8.4438@edtnps89...
Hi Reggie

I think this will work for you...

************************************************** *********
Private Sub cmdExportQuery_Click()

Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rst As DAO.Recordset
Set rst = MyDB.OpenRecordset("qryReggie", dbOpenDynaset)

Open "C:\TEMP\TESTFILE.txt" For Output As #1 ' Open file for output.
'Note: The directory that will contain the text file (in this case
"C:\TEMP") must exist.

With rst
.MoveLast 'Populate the recordset
.MoveFirst
Do Until .EOF
'Print the contents of the first set of fields on the first line
Print #1, !Nomen; Tab(22); !PartNo; Tab(36); !TackNo

'Print the contents of the Remarks field on the second line
Print #1, !Remarks

Print #1, 'Print a third (blank) line to delineate the record

If Not .EOF Then .MoveNext
Loop
Close #1 'Close the text file.
.Close 'Close the recordset
End With

Set rst = Nothing
Set MyDB = Nothing

End Sub
************************************************** *********

--
--
HTH,
Don
=============================
E-Mail (if you must) My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

================================================== ========================
I was "anally raped" on a Timeshare deal by Club All Seasons
http://worf.usshurdman.com/~calgary/...id=26180&code=
, Les Volieres du Quebec, and Club Privilege.

In appreciation for that, please feel free to forward SPAM and VIRUSES to:
ch**********@bellnet.ca
ip*****@hotmail.com
in**@volieres.com

Don't get mad --- get even! :-)
================================================== ========================

"Reggie" <NoSpam_chief123101@NoSpam_yahoo.com> wrote in message
news:k4********************@comcast.com...
Hi and TIA! I have a query that I want to save as a fixed-width text

file. I can get the export to
work using an export specification with one exception. This is a

government message and they need
the remarks field to print on a separate line from the details section and

I can' figure out how to
get the remarks to start on a new line. So what I want is (Start Column &

Width in parenthesis)

Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)
Nomen(1 - 21) PartNo(22 - 14) TackNo(36 - 15)
Remarks (1 - 50)

Is this possible? I've looked through all my literature and the NG, but

have yet to come up with a
solution. Any advice or pointers as to where I can look up more info is

appreciated. As always,
Thanks for your time!

--
Reggie

----------


Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.