Export Fixed Length Field

[Guest]

I need to create an export (text) file in which the "fields" are of a fixed
length and in some cases of a different length to those in the table/query.
For instance, on the example below, "txtacctnumb" need to be 20 characters in
length whereas in the table it is 10.

Do While Not rs.EOF
With rs
strAcct = strAcct + !txtAcctNumb
strAcct = strAcct + !txtAcctTitle
strAcct = strAcct + !txtAcctAddress1
strAcct = strAcct + !txtAcctPostCode
End With
Print #(iOFileNr), strAcct
rs.MoveNext
Loop

I would be grateful for any assistance on how to achieve this.

Thanks,

Dave

 

[Guest]

Pass your variables through a function like this:

Public Function paddString(ByVal strString As String, ByVal intPaddTo As
Integer) As String
Dim intOriginalLength As Integer
Dim i As Integer
intOriginalLength = Len(strString)
If intOriginalLength >= intPaddTo Then
paddString = strString
Exit Function
End If
For i = 0 To intPaddTo - intOriginalLength
strString = strString & " "
Next i
paddString = strString
End Function

Note that this function returns the original string if it is longer than or
equal to the length you want it padded to (intPaddTo).
Hope this helps.

"Dave C" schreef:

 

[Guest]

First, don't use the + for concatenating strings. The correct operator is &.
+ if for arithmatic. The Space() Function will do what you want; however,
the problems is when you want twenty characters and there could be 30. I
don't know what you want to do in that case, but in the example below, I
truncate the variable to the desired length, so in the case above, only the
first 20 characters would be exported. I will make up lengths since I don't
know the real ondes.

Do While Not rs.EOF
With rs
strAcct = strAcct & _
IIf(Len(!txtAcctNumb) > 20, Left(!txtAcctNumb,20), _
!txtAcctNumb & Space(20 -Len(!txtAcctNumb)))
strAcct = strAcct & IIf(Len(!txtAcctTitle) > 30, _
Left(!txtAcctTitle,30), !txtAcctTitle & Space(30 -
Len(!txtAcctTitle)))
........
.........
End With
Print #(iOFileNr), strAcct
rs.MoveNext
Loop

 

[John Nurick]

First, don't use the + for concatenating strings

.... except when you want to take advantage of its magic properties:

a + b <--> IIF(Not IsNull(a) And Not IsNull(b), a & b, Null)

 

[Guest]

Did not know that. clever, however, doesn't get many points on the Self
Documenting Code scale. Although more verbose, the later example is more
explicit to us human types.