Output table fields as fixed length to a text file 1

[egstatus]

Hi all,
I have a table that I want to output to a text file as fixed lenght records. However before I output all the records I need to output a header record.

I have tried the print command but this command does not output as fixed length. It treams the fields. I need the fields to be fixed length regarless of what is stored on each field.

Here is the code I am using.

I = 0
OutputLine = ""

'Output the Header Record
Print #FileNum, "P O BOX 5730 " & " " & RS.RecordCount

'start outputting the data
Do Until RS.EOF
For I = 0 To RS.Fields.Count - 1
OutputLine = OutputLine & RS.Fields(I).Value
Next I
Print #FileNum, OutputLine
I = 0
OutputLine = ""
RS.MoveNext
Loop

How can I acomplish to output each field as fixed length?

Thanks in advance.

EG

 

[HarleyQuinn]

You could pad the fields with Spaces, something like (for a field length of 30)

Left(RS.Fields(I).Value & Space(30),30)

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[egstatus]

The problem lies that my fields have different fields sizes.

For example I have:
FName as 20
LName as 20
Address1 as 30
Address2 as 30
City as 20
State as 2
ZipCode as 9
Etc, etc.

Therefore in the loop I need to know wich field is being output in order to pad with spaces.
I was thinking that there must be a better way of handling this.
If there is one, can someone please share it?

Thanks

 

[randy700]

Do you have a method of determining the maximum length of each field? If so, you could modify HarleyQuinn's code, something like this:

Left(RS.Fields(I).Value & Space([COLOR=red]FieldLength[/color]),[COLOR=red]FieldLength[/color])

Randy

 

[RoyVidar]

I think the defined size of a (DAO) field, should be available through the field property .size, so perhaps something along these lines?

[tt]OutputLine = OutputLine & RS.Fields(I).Value & Space(rs.fields(I).size - _
len(rs.fields(I).value & vbnullstring))[/tt]

In case you need to differensiate between different datatypes, you might need for instance

[tt]if (rs.fields(I).attributes and dbText) = dbText then
' it's a text field
end if[/tt]

Roy-Vidar

 

[PHV]

You may use the Field.Size (DAO) or Field.DefinedSize (ADO) property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[egstatus]

That did it Roy-Vidar.

Thank you and all who contributed to solve this issue.

EG.