File conversion: Force blank-filled fields

Question

I am very inexperienced at file conversions and I've run across a major problem. In Access97. I am exporting a file to ASCII and some of the fields are required to have a certain fixed width with blank spaces (not zeros). For example, the specs are: 

The problem is that the Access table that I am exporting has the fields as separate ones (i.e., diagnosis_code1 and diagnosis_code2). So I concatenated them and put spaces between them, but for the data that is not six bytes long, Access doesn't use the six spaces and throws everything off whack. I tried using the format character @ to force six spaces, but then it right-aligns the data. The only data alignment function I could find is Rset and Lset, but they work only by assigning the data to a new variable.

Does anyone know a way to either force the alignment of the data in the field or to force blank-filled spaces?

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: Garnette Lilly-Price <techtrain@NEWWAVE.NET>
Cc: ACCESS-L <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: File conversion: Force blank-filled fields?
Date: 3 June 1998 11:54

Garnette,

You can use this function as template to concatenate fields on export:

Const mcintFld1MaxLen As Integer = 15

Public Function MyConcat(ByVal vvarFld1 As Variant, _
                         ByVal vvarFld2 As Variant)
                         
    On Error Resume Next
    Dim intFld1Len As Integer
    
    intFld1Len = Len(vvarFld1)
    If intFld1Len > mcintFld1MaxLen Then
        intFld1Len = mcintFld1MaxLen
    End If
    MyConcat = Left(vvarFld1, intFld1Len) & _
               Space(mcintFld1MaxLen - intFld1Len) & _
               vvarFld2
End Function

You don't need to use make table queries - you can export just queries themselves...

HTH,
Shamil