MainFrame upload file

[Guest]

I have data that has been pulled in from a query in access and need to format
this data into the proper specs for upload to a MainFrame. I want
to pad some of the fields with either an empty space or with zeroes. When I
export however, I am unable to save the file with the proper formatting (no
matter
how I save this file). I end up with a tab delimited file instead of being
able to combine theses fields with the proper zeroes or spaces. Can anyone
help with this?

Example:
1-2 record code
3-14 Numeric
15-24 Numeric
25-36 Amount
37-44 Date
etc,etc...

Rick

 

[Andi Mayer]

I have data that has been pulled in from a query in access and need to format
this data into the proper specs for upload to a MainFrame. I want
to pad some of the fields with either an empty space or with zeroes. When I
export however, I am unable to save the file with the proper formatting (no
matter
how I save this file). I end up with a tab delimited file instead of being
able to combine theses fields with the proper zeroes or spaces. Can anyone
help with this?

Example:
1-2 record code
3-14 Numeric
15-24 Numeric
25-36 Amount
37-44 Date
etc,etc...

Rick

you mean something like this?

set rs............
dim tmp as string
while not rs.eof
tmp=format(rs.fields(0),"@@")
tmp=tmp &format(rs.fields(1),"00000000")
............
tmp=tmp &vbcrlf
rs.movenext
wend

open "C:\myFile" for output as #1
print #1,tmp
close #1

 

[SirPoonga]

Make sure you read the help info or msdn info for the format function
in access. There's a bunch of formatting symbols to use.

*IF* you need to do it through code I have plenty of examples. That's
what I am doing now. Converting data from one mainframe system to
another along with cleaning the data. I hope you don't have to deal
with things like negative numbers being represented as letters, then
you may have to deal with code. Also, do you have floating point
numbers to deal with?

 

[Albert D. Kallal]

You can make a export spec, and NOT have to write a bunch of code.


The export wizard in ms-access supports the crating of fixed length data
output as you ask.

When you export the table, you need to choose fixed length, and NOT
delimited data (since, as you mention, you don't want delimited data...so
don't choose the delimited option when you export to that text file.

ms-access can (will) pad/put in the extra spaces for you when you export.

As for some numbers that need extra padding, you should in fact build query.
The advantages of using a query are many, and you even set the sort order of
the export.

To pad a field to 5 digits, you can enter a expression in the query builder
like:

MyNum: format([numField],"00000")

So far, from what you asked for, you don't need yet to resort to code.
However, if you can't used the export wizard and a query with some
expressions, then you can always resort to writing code to make the export
as Andi suggests in the other answer here.

 

[Guest]

Thanks to everyone for thier response. At this point, I don't need to do any
coding. I will utilize the fixed length solution and see if it will do
everything I need. I appreciate your help.

Thank you,

Rick