Exporting to Fixed Width Right Align.

[vvariety]

Hello Everyone,

I have a table I need to export most fields are left align but there are two that are right align, I have tried to use the Space Function but it does not work well, I have also tried using the Rset in a vba module and thought it was working but now it is not. I'm stumped and could use some expertise in this area

Thanks in advance for your responses.

 

[vbaInet]

The right alignment is a control property in Access so if you want to preserve that formatting you need to tell it do so. Use the Import/Export Wizard to export the file and select "Export with Formatting and Layout".

 

[vvariety]

thank you for your response, but I have the export strictly in VBA, Since this is coming from a make table which is temporary. So VBA builds the text file from this make table and I would like to format it on the Export. Acording to what I have been able to read the RSet and LSet functions should right align and left align the specified field. The output should look like

1001 100-1
10021002-1

My result however looks like this:
1001100-1
10021002-1

My code is:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer
Dim strFileName As String
Dim strAcctNo As String * 4
Dim strWellID As String * 6
Dim strJournalNo As String * 3
Dim strReference As String * 8
Dim strDescription As String * 30
Dim strAmount As String * 15
Dim strQuantity As String * 15
Dim strEffDate As String * 8
Dim strTextFileName As String

'Kill strTextFileName
strTextFileName = Forms![frmlaborcharges]![txtFileDest]

Set db = CurrentDb
Set rst = db.OpenRecordset("tmpexportLabor")
intFile = FreeFile
Open strTextFileName For Output As intFile

rst.MoveFirst
Do Until rst.EOF
LSet strAcctNo = rst![AccountNumber]
RSet strWellID = rst![Well_ID]
RSet strJournalNo = rst![JournalNum]
LSet strReference = "PUMPING"
LSet strDescription = "ALLOCATE FIELD LABOR TO WELL"
LSet strAmount = Format(rst![Amount], "000000000000.00")
LSet strQuantity = Format(rst![Quantity], "000000000000.00")
LSet strEffDate = rst![EffectiveDate]
Print #intFile, strAcctNo & strWellID & strJournalNo & strReference & strDescription & strAmount & strQuantity & strEffDate
rst.MoveNext
Loop
Close intFile
rst.Close
db.Close

 

[vbaInet]

I see, you're setting the fixed width in code. I initially thought you just wanted to right align the text and that you had that set in your table already.

Can you enclose your code in code tags so that it preserves the formatting and also do the same for the before and after as well.

http://www.access-programmers.co.uk/forums/showthread.php?t=200247

 

[vbaInet]

The output should look like

1001 100-1
10021002-1

My result however looks like this:
1001100-1
10021002-1

You're talking about WellID right?

Have you tried other functions like Spc(), Space() and String()?

There are other ways of achieving your objective.

 

[vvariety]

yes, i am referencing the wellid I have tried the space() functions will still the same results.

Function ExportLabor()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer
Dim strFileName As String
Dim strAcctNo As String * 4
Dim strWellID As String * 6
Dim strJournalNo As String * 3
Dim strReference As String * 8
Dim strDescription As String * 30
Dim strAmount As String * 15
Dim strQuantity As String * 15
Dim strEffDate As String * 8
Dim strTextFileName As String

'Kill strTextFileName
strTextFileName = Forms![frmlaborcharges]![txtFileDest]

Set db = CurrentDb
Set rst = db.OpenRecordset("tmpexportLabor")
intFile = FreeFile
Open strTextFileName For Output As intFile

rst.MoveFirst
Do Until rst.EOF
    LSet strAcctNo = rst![AccountNumber]
    RSet strWellID = rst![Well_ID]
    RSet strJournalNo = rst![JournalNum]
    LSet strReference = "PUMPING"
    LSet strDescription = "ALLOCATE FIELD LABOR TO WELL"
    LSet strAmount = Format(rst![Amount], "000000000000.00")
    LSet strQuantity = Format(rst![Quantity], "000000000000.00")
    LSet strEffDate = rst![EffectiveDate]
    Print #intFile, strAcctNo & strWellID & strJournalNo & strReference & strDescription & strAmount & strQuantity & strEffDate
    rst.MoveNext
Loop
Close intFile
rst.Close
db.Close
ErrHandlerExit:
    Exit Function
ErrHandler:
    DoCmd.Echo True
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume ErrHandlerExit

End Function

 

[vbaInet]

Can I see how you used Spc() and Space()? Or upload a test db.

Anyway, what you're trying to do can be easily achieved using a query and the Import/Export wizard. You can set fixed length widths and save it as a Spec. Then if you wish run the spec in code.

 

[vvariety]

in a query

Field A: Acctno
Field B: Space(6-len(wellid))

If I create the Make Table and then use that to export I get the same result. That is why I am Stumpped. By the way thank you for the assistance.

 

[vbaInet]

Not a Make Table query, it's probably chopping off the space. Here's what I mean:

1. Create a query with FieldA: AcctNo, FieldB: Space(6 - Len(WellID)) & WellID, FieldC: AcctNo & Space(6 - Len(WellID)) & WellID
2. Save and export this query to a text file

 

[vvariety]

Thank you that worked much better

 

[vbaInet]

Good to hear!

I'm still a bit intrigued that your original code didn't work so if you've got time, upload a test db and I'll look into it.