Export Specifications

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I hope someone here can help. I have a file that has about 20 fields in it,
and I need to combine about 4 fields into one field. But, they need to be
formatted. Some needs to be subst other fields need to be padded with 0's at
the end or at the beginning. Can this be accomplished with the export specs?
Any help is appreciated.
 
Export said:
Hello,
I hope someone here can help. I have a file that has about 20 fields
in it, and I need to combine about 4 fields into one field. But, they
need to be formatted. Some needs to be subst other fields need to be
padded with 0's at the end or at the beginning. Can this be
accomplished with the export specs? Any help is appreciated.
Click to expand...

Create a query with the output you want and then export the query.
 
If your field is character based and needs to be right justified try:
Field: MyRightText: Right$(Space(10) & MyField,10)

If your field is numeric and needs leading zeroes use a similar technique to
return leading zeroes.
Field: MyRightText: Right$("0000000000" & MyField,10)

For decimal problems:
Create a query with the fields that you want to export and use the Format()
function to specify the number of decimal places in the number field. For
example, if your field has 4 decimal places, the column in the query grid
would look as follows:

Field: Format([MyField],"##0.0000")


================================================
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

--
Joe Fallon
Access MVP



"Export Specifications help" <Export Specifications
(e-mail address removed)> wrote in message
 
Back
Top