To have a fixed number of space between each field when exporting data.

[sharonbl]

Hi..
Anyone here knows if there's any way that u can fixed the number of spaces between each field?Otherwise the other application will not be able to read in the actual data.
Thanks

 

[Pat Hartman]

Use a query to select the data you want to export. Format the numeric fields using the Format() function so they will have leading zeros and a fixed number of decimal places. You can also format the date fields in whatever manor that the receiving program needs. The two formats you'll use are:

Select Format(NumericField,"000000.00") as OutField, Format(DateField,"yyyymmdd") as OutDate, etc.
From YourTable;

Use the TransferText Method or Action and specifiy an export specification. But first you'll need to export the file once manually so you can make an export spec to get the field lengths set properly.

 

[sharonbl]

Hi
Thanks for the solution...
I think I understand what you said..
I have another question here regarding the leading zeros. If I want the length to be 12 and to append the zeros infront of the number, can I just use the Format function but specify in the table that this field to be 12 digits?

 

[Atomic Shrimp]

Hi Sharon

I know this is not exactly a solution to your question, but you might find it useful nonetheless.
I had a situation where i had to write EDI data files which are essentially fixed-width text files, but very strictly formatted; in my VBA code I had to format strings in all sorts of ways, so i made myself a custom formatting function, it looks like this:

Function padtrim(mystr As String, length As Integer, rightJ As Boolean, padchar As String)
mystr = Trim(mystr)
padtrim = mystr
If Len(mystr) > length Then
If rightJ = True Then
padtrim = right(mystr, length)
Else
padtrim = Left(mystr, length)
End If
End If

If Len(mystr) < length Then
If rightJ = True Then
padtrim = String(length - Len(mystr), padchar) & mystr
Else
padtrim = mystr & String(length - Len(mystr), padchar)
End If
End If
End Function

The function is used by passing it your string, the length you want back, A boolean value indicating if you want right or left justified, and the character with which you want to pad out the string if too short.

So if you do:
newstring = padtrim("123",10,True,"0") you get "0000000123"
newstring = padtrim("123",10,False,"0") you get "1230000000"
newstring = padtrim("123",5,True," ") you get " 123"
newstring = padtrim("123",5,False," ") you get "123 "
newstring = padtrim("1234567",5,True," ") you get "34567"
newstring = padtrim("1234567",5,False," ") you get "12345"

This is called 'procrustean' string handling, named after an innkeeper in Greek mythology called Procrustes who, if his guests did not fit the beds, either stretched them on a rack, or cut off their feet!

HTH

Mike

 

[Pat Hartman]

If you want the length to be 12, use 12 zeros. If you want the length to be 2, use 2 zeros. Get the picture

 

[pungentSapling]

help with leading zeros

I am trying to export a query to text. The hours field needs to contain 5 bytes, for instance 40 hrs should look like 04000 I can get my dynaset to look like this but when I export to a text file I lose the leading zeros. I have tried to follow the advice I found from a previuos posting (quoted below) but to no avail. I have the format of the field set to 00000.
I tried inserting the code into the SQL of the query following the model below. But I always lose the preceeding zeros, the end zeros are in place.
thanks, p

Use a query to select the data you want to export. Format the numeric fields using the Format() function so they will have leading zeros and a fixed number of decimal places. You can also format the date fields in whatever manor that the receiving program needs. The two formats you'll use are:

Select Format(NumericField,"000000.00") as OutField, Format(DateField,"yyyymmdd") as OutDate, etc.
From YourTable;

Use the TransferText Method or Action and specifiy an export specification. But first you'll need to export the file once manually so you can make an export spec to get the field lengths set properly.