Export txt file Specs (1 Viewer)

[Treason]

I have a query that I am exporting to a Fixed Width txt file so I can upload it into another application. The problem is the application I am uploading to does not allow spaces in between fixed width columns, instead they want to use an "&". So I am looking to change the spaces in between the fields to another character, "&" for example, if this was the exported txt file...

JOHN DOE 1234
JANET DOE 5678
JAMES RIELLY 9012
I need it to change the spaces in between the fields into "&" like this:

JOHN DOE&&&&&1234
JANET DOE&&&&5678
JAMES RIELLY&9012

In this example the default width of the name field is 13 characters. So if a name only takes up 10 characters then fill &&& at the end of the field. I hope I am asking this correctly

 

[antomack]

You could use a query to format your data to the required format and then export the query to get correct export file.

You can use the following functions to add leading or trailing &'s as required.

TrailingPad([YourFieldName],"&",YourRequiredLength)

Using it on the field containing the names would return
John Doe&&&&&
from
John Doe if you required a length of 13.

Public Function LeadingPad(str As String, strPad As String, intLen As Integer)
  ' adds leading character strPad to str to make string of length intLen
  ' if length of str > intLen then returns str with no changes
  
  Do While Len(str) < intLen
    str = strPad & str
  Loop
  LeadingPad = str

End Function

Public Function TrailingPad(str As String, strPad As String, intLen As Integer)
  ' adds trailing character strPad to str to make string of length intLen
  ' if length of str > intLen then returns str with no changes
 
  Do While Len(str) < intLen
    str = str & strPad
  Loop
  TrailingPad = str

End Function