Here is one way to export data to a fixed width file without the wizard:
Some advantages include easy updating of a spec change, easy exporting of
leading zeroes (notoriously difficult by the way) and a simple trick for
right justifying data.
Sub ExportData(strExportFile As String)
'strExportFile is the full path and name where you would like to create
and save the output file
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer
'get file handle and open for output
intFileNum = FreeFile()
'opens the disk file
Open strExportFile For Output As #intFileNum
'open the recordset
Set rs = CurrentDb.OpenRecordset("MyTableOrQueryName", dbOpenSnapshot)
'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![key] & Space(11 - Len(![key])) '1-11
strData = strData & ![TransType] '12
'this is how to right justify an entry
strData = strData & Space(14 - Len(Format(![Qty], "0.0000"))) &
Format(![QtyRcv], "0.0000") '13-26
strData = strData & Format(![TransDate], "mm/dd/yyyy") '27-36
strData = strData & Format(![Date1], "mm/dd/yyyy") '37-46
strData = strData & Format(![Date2], "mm/dd/yyyy") '47-56
strData = strData & Format(![Date3], "mm/dd/yyyy") '57-66
strData = strData & Format(![Date4], "mm/dd/yyyy") '67-76
strData = strData & ![Num] & Space(10 - Len(![Num])) '77-86
strData = strData & ![Status] '87
strData = strData & ![Reason] '88
'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With
Close #intFileNum
rs.Close
Set rs = Nothing
MsgBox (strExportFile & " has been created.")
End Sub
========================================================
As a bonus:
Here is a sample of some general export code:
Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)
'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??
Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer
'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)
'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum
If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next
'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)
'write out the header row
Print #intFileNum, varData
End If
'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next
'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)
'write out data row
Print #intFileNum, varData
rs.MoveNext
Loop
Close #intFileNum
rs.Close
Set rs = Nothing
End Sub
--
Joe Fallon
Access MVP
jrenzul said:
Seems I've narrowed down the problem to the actual export Wizard. Is the
only
way to fix this to write my own export wizard? I see in the help text that
this can be done.
jrenzul said:
Expr13:
Format((IIf(LAWSON_EMPLOYEE!SALARY_CLASS="S",(Round((LAWSON_EMPLOYEE!PAY_RATE/52/40),2)),LAWSON_EMPLOYEE!PAY_RATE)*100),String(8,"00000000"))
The above is the expression for this field in the query
Then I select File/Export -Save as Type: Text File
Fixed Width
Even up to this point, the field is still showing the way I want it to.
When
the Export Text Wizard finishes, however, this field has .00 added to the
end
of the field.
jrenzul said:
The 100 multiplier is part of my field "payrate". In the query output,
the
format is exactly like I want it, which is 00001500. However, when the
final
text file is created, the system still sees this as a number and not a
string. It adds .00 to the end of the number, making it 1500.00. I've
tried
changing the Regional Controls, but there is still a decimal point
required,
so then the output is 1500. I need to get rid of the decimal point.
:
On Tue, 15 Feb 2005 06:51:01 -0800, "jrenzul"
I am exporting to a flat file and am having an issue with a numeric
field. I
want to show this field with leading zeros and no decimal points
(ie.
00001500 for $15.00). I formatted the field in the query with
Format([payrate],String(8,"0")), but when the file is exported, this
field
still shows decimal points. Any suggestions?
if you need it every time in cents then multply [payrate] with 100
