Convert data to consistent number of characters

[Guest]

I've got a database where I need to concatenate fields in order to export to
a flat file. The file will be imported to a system that is parsing out
values based on character location, almost like a fixed width file, but each
field has to fill out the alloted space. Example:

AccountID - characters 1-10
Name - characters 11-50
State - characters 51-52

In this example, the values I have for AccountID and State are consistently
10 and 2 characters, however I have variable lengths for the Name field. I
need all of them to be 40 characters in length. I've written the following
code to look at the current length in the field, determine how many
characters to add to get to 40 and then add the number of spaces one at a
time:

Dim AddThis As Integer
Dim i As Integer

If Len(Name) < 40 Then
AddThis = 40 - Len(Name)
For i = 1 To AddThis
Name = Name & " "
Next i
End If

My question is, how do I trigger this bit of code to evaluate each record in
a table and update the value in the table. Because of the For...Next
statement I don't think this can be run as part of an Update Query.

Any suggestions will be welcomed.

 

[Marshall Barton]

I've got a database where I need to concatenate fields in order to export to
a flat file. The file will be imported to a system that is parsing out
values based on character location, almost like a fixed width file, but each
field has to fill out the alloted space. Example:

AccountID - characters 1-10
Name - characters 11-50
State - characters 51-52

In this example, the values I have for AccountID and State are consistently
10 and 2 characters, however I have variable lengths for the Name field. I
need all of them to be 40 characters in length. I've written the following
code to look at the current length in the field, determine how many
characters to add to get to 40 and then add the number of spaces one at a
time:

Dim AddThis As Integer
Dim i As Integer

If Len(Name) < 40 Then
AddThis = 40 - Len(Name)
For i = 1 To AddThis
Name = Name & " "
Next i
End If

My question is, how do I trigger this bit of code to evaluate each record in
a table and update the value in the table. Because of the For...Next
statement I don't think this can be run as part of an Update Query.

This expression would be easier and more efficient:
Left([Name] & String(40, " "), 40)

Now you can create a query that adjusts and concatenates the
fields all in one step:

SELECT AccountID & Left([Name] & String(40, " "), 40) &
State As BigField
FROM yourtable

and then use TransferText or the menus to export the query
to a text file.

 

[Guest]

Perfect!!! Thanks, Marshall.

Dave

I've got a database where I need to concatenate fields in order to export to
a flat file. The file will be imported to a system that is parsing out
values based on character location, almost like a fixed width file, but each
field has to fill out the alloted space. Example:

AccountID - characters 1-10
Name - characters 11-50
State - characters 51-52

In this example, the values I have for AccountID and State are consistently
10 and 2 characters, however I have variable lengths for the Name field. I
need all of them to be 40 characters in length. I've written the following
code to look at the current length in the field, determine how many
characters to add to get to 40 and then add the number of spaces one at a
time:

Dim AddThis As Integer
Dim i As Integer

If Len(Name) < 40 Then
AddThis = 40 - Len(Name)
For i = 1 To AddThis
Name = Name & " "
Next i
End If

My question is, how do I trigger this bit of code to evaluate each record in
a table and update the value in the table. Because of the For...Next
statement I don't think this can be run as part of an Update Query.

This expression would be easier and more efficient:
Left([Name] & String(40, " "), 40)

Now you can create a query that adjusts and concatenates the
fields all in one step:

SELECT AccountID & Left([Name] & String(40, " "), 40) &
State As BigField
FROM yourtable

and then use TransferText or the menus to export the query
to a text file.