export to text file

[fule12]

If sameone cant help me with exporting form to text file.
I make module :
Private Sub cmbExport_Click()
Dim db As Database, rst As Recordset
Dim a As Integer, OrderNo, Vessel, AccCode, RDate, Cost, Myfile As String
Set db = CurrentDb
Myfile = "C:\_Nikola\test.txt"
Set rst = db.OpenRecordset("Query2&quot
rst.MoveLast
rst.MoveFirst
Open Myfile For Output As #1
    For a = 1 To rst.RecordCount
    OrderNo = Trim(rst!OrderNo) & Space(25 - Len(Trim(rst!OrderNo)))
    AccCode = Trim(rst!Code) & Space(8 - Len(Trim(rst!Code)))
    Vessel = Trim(rst!VesselCode) & Space(5 - Len(Trim(rst!VesselCode)))
    RDate = Trim(rst!Date) & Space(10 - Len(Trim(rst!Date)))
    Cost = Trim(rst!EstCostUSD) & Space(15 - Len(Trim(rst!EstCostUSD)))
    Print #1, OrderNo & Vessel & RDate & Cost
    rst.MoveNext
    Next
    Close #1
    rst.Close
    db.Close
End Sub
and is work OK but i nead to things:
1.how to make header and footer on text file to be like this:
this is my header
AFR-D6/02            AFR  25/01/02  269.00
AFR-DFM2/02          AFR  31/01/02  2,500.00
AFR-E9b/02           AFR  31/01/02  0.00 
AFR-D5/02            AFR  25/01/02  2,570.00
AFR-E24/02           AFR  21/02/02  0.00
AFR-E10/02           AFR  28/01/02  29,140.00
AFR-DFM9/02          AFR  14/03/02  0.00
AFR-DFM7/02          AFR  21/02/02  0.00
AFR-D7/02            AFR  29/01/02  0.00
AFR-E17/02           AFR  08/02/02  367.00
AFR-E25/02           AFR  21/02/02  0.00
AFR-E14/02           AFR  24/01/02  365.00
AFR-E13/02           AFR  24/01/02  0.00
AFR-E12/02           AFR  24/01/02  0.00
AFR-E11/02           AFR  24/01/02  306.00
AFR-DFM6/02          AFR  08/02/02  50.00
AFR-E27/02           AFR  05/02/02  0.00
this is my footer
this is my header    
ALC-D49/02           ALC  25/02/02  1,210
ALC-E27/02           ALC  23/01/02  500.00
ALC-D44/02           ALC  14/02/02  0.00
ALC-D13/02           ALC  22/01/02  609.00
ALC-D16/02           ALC  22/01/02  251.00
ALC-D46/02           ALC  14/02/02  240.00
ALC-D47/02           ALC  18/02/02  977.00
ALC-E25/02           ALC  22/01/02  506.00
ALC-D15/02           ALC  22/01/02  0.00 
ALC-D50/02           ALC  25/02/02  450.00
ALC-E20/02           ALC  22/01/02  3,680.00
ALC-DFM24/02         ALC  14/02/02  2,734.00
ALC-E15/02           ALC  22/01/02  398.00
ALC-D41/02           ALC  23/01/02  1,780.00
ALC-DFM18/02         ALC  28/01/02  7,375.00
ALC-E45/02           ALC  08/02/02  821.00
ALC-DFM17/02         ALC  28/01/02  7,375.00
ALC-E46/02           ALC  13/02/02  0.00
ALC-DFM33/02         ALC  13/03/02  1,558.00
ALC-E39/02           ALC  01/02/02  500.00
this is my footer        
this is my header
ALC-D19/02           ALE  22/01/02  468.00
ALC-D35/02           ALE  22/01/02  1,142.00
ALC-D34/02           ALE  22/01/02  1,216.00
ALC-D33/02           ALE 22/01/02  0.00
and etc..etc
so records need to be separated with header and footer when vessel Code Change (AFR,ALC,ALE ....) and
secend question:
i nead to make total on end of each group of records(so bifore end of footer is nead to be line like this:
this is my header
ALC-D19/02           ALE  22/01/02  468.00
ALC-D35/02           ALE  22/01/02  1,142.00
ALC-D34/02           ALE  22/01/02  1,216.00
ALC-D33/02           ALE 22/01/02  0.00
3151 TOTAL ACR ALE 31/03/02 2,826.00
this is my footer
this is my header
etc...etc..

Please Help
Thanks
Fule Fule

 

[JohnLowell]

I haven't tested this code, but it should give you a good start in the right direction.

Private Sub cmbExport_Click()
Dim db As Database, rst As Recordset
Dim a As Integer, OrderNo, Vessel, AccCode, RDate, Cost, Myfile As String
Dim strHold as string
Dim dblSum as double

Set db = CurrentDb
Myfile = "C:\_Nikola\test.txt"
Set rst = db.OpenRecordset("Query2&quot
rst.MoveLast
rst.MoveFirst
Open Myfile For Output As #1
Print #1, "This is my header"
strHold = Trim(rst!VesselCode) & Space(5 - Len(Trim(rst!VesselCode)))

For a = 1 To rst.RecordCount
OrderNo = Trim(rst!OrderNo) & Space(25 - Len(Trim(rst!OrderNo)))
AccCode = Trim(rst!Code) & Space(8 - Len(Trim(rst!Code)))
Vessel = Trim(rst!VesselCode) & Space(5 - Len(Trim(rst!VesselCode)))
RDate = Trim(rst!Date) & Space(10 - Len(Trim(rst!Date)))
Cost = Trim(rst!EstCostUSD) & Space(15 - Len(Trim(rst!EstCostUSD)))

If Vessel <> strHold then
' print footer total and footer msg
Print #1, TOTAL &quot; & strhold & &quot; &quot; & now() & &quot; &quot; & dblSum
Print #1, &quot;This is my footer&quot;
' print header msg for new vessel
Print #1, &quot;This is my header&quot;
dblSum = cdbl(Cost)
strHold = Vessel
else
dblSum = dblSum + cdbl(Cost)
endif
Print #1, OrderNo & Vessel & RDate & Cost
rst.MoveNext
Next
' now print out the remaining numbers because you ran out of data
Print #1, TOTAL &quot; & strhold & &quot; &quot; & now() & &quot; &quot; & dblSum
Print #1, &quot;This is my footer&quot;
Close #1
rst.Close
db.Close
End Sub

 

[fule12]

John thanks a loooooot for code!
my rooki in vba programming i was trying your code and is give me error on this line:

strHold = Vessel
else
dblSum = dblSum + cdbl(Cost) - error
endif

error is
Run-time error'13'
type mismathc

in query field Cost look like this :
Cost: Format([EstCostUSD],&quot;Standard&quot

any idea what i nead to change?
thanks Fule

 

[JohnLowell]

In the function you have Cost declared as a string. I'm trying to convert it to a double, so you can sum the Cost values for each vessel.

try this:

Replace dblSum = dblSum + cdbl(Cost) with

dblSum = dblSum + cdbl(trim(Cost))

 

[KippL]

Hey guys,
That's a TON of code........why not put your functions trim & pad with spaces) into a query.......each field as needed and then when you've got your text formatting all set do a simple docmd.transfer text,delimited destination file?
Just food for thought (and I HATE to type mounds of code) unless I have to!
Kipp

 

[fule12]

i try but is same error !!! Fule