Output of query help.

[SteveE]

I have a table (impoted) which has several fields from which my customer demand I produce a delimited text file exactly as the sample below, I can create the data portion no issues but I need to include 2 header lines in the output file i.e the IBO and the IBD lines as below

IBO|G1234567|3.60
IBD|1010012345| 1DE5XN |400|U|G1234567
SRN|4010101010|G1234567|354203038293595|||1DE5XN
SRN|4010101010|G1234567|354203038293603|||1DE5XN
SRN|4010101010|G1234567|354203038293835|||1DE5XN
SRN|4010101010|G1234567|354203038293785|||1DE5XN


All the data is contained in the same table but repeated line by line for the IBO and IBD data

S 2PO PTN SERNo A B IBO Sver D ProdC PQty E DOrder
SRN 4010101010 G1234567 354203038293595 IBO 3.60 IBD 1DELL5XN 400 U 1000001
SRN 4010101010 G1234567 354203038293603 IBO 3.60 IBD 1DELL5XN 400 U 1000001
SRN 4010101010 G1234567 354203038293785 IBO 3.60 IBD 1DELL5XN 400 U 1000001

Can anyone suggest a way of producing this or suggesting which way I should go on this.

Steve

 

[gemma-the-husky]

you probably need to produce the file by explicitly writing rows, rather than saving a query

ie, basically

openfile
for each record you need to save
   write header lines  
   write detail lines
next
closefile

 

[SteveE]

Dave many thanks for you assisitance here
I have been doing a bit of research and have gotten what I need with one exception the output is commor delmited I need pipe the code I have used is as below I think it waht you were refering to

Dim MyDB As DAO.Database
Dim rstO2 As DAO.Recordset
Dim intO2 As Integer
intO2 = FreeFile()
Open "C:\ImportFiles\O2Data" & Format(Now, "mmyyhhmm") & ".txt" For Output As #intO2
Set MyDB = CurrentDb()

Set rstO2 = MyDB.OpenRecordset("O2Export", dbOpenForwardOnly)

With rstO2
Write #intO2, ![IBO], ![PTN], ![Sver]
Write #intO2, ![IBD], ![O2PO], ![O2ProdC], ![PQty], !, ![PTN]
Do While Not .EOF
Write #intO2, ![SRN], ![O2PO], ![PTN], ![SerNo], "", "", ![O2ProdC]
.MoveNext

Loop

End With
Close #intO2
rstO2.Close
Set rstO2 = Nothing

This produces the correct file with all the data and headers apart form the export specification can this bw used with this methord?

"IBO","G1234567","3.60",
"IBD","4010101010","1DELL5XN ","400","U","G1234567",
"SRN","4010101010","G1234567","354203038293595","","","1DELL5XN ",
"SRN","4010101010","G1234567","354203038293603","","","1DELL5XN ",
"SRN","4010101010","G1234567","354203038293785","","","1DELL5XN",
"SRN","4010101010","G1234567","354203038293835","","","1DELL5XN ",
"SRN","4010101010","G1234567","354203038293836","","","1DELL5XN ",
"SRN","4010101010","G1234567","354203038293837","","","1DELL5XN ",

once again thanks for your help
Steve

 

[SteveE]

Many thanks for the help I have received here I have just 1 issue left with this problem, I have resolved the issue of outputting with a Pipe delimiter as below

Function ExportO2Pipe()
On Error GoTo Err_ExpO2Pipe

Dim O2DB As DAO.Database
Dim rstO2 As DAO.Recordset
Dim intO2 As Integer
intO2 = FreeFile()
Open "C:\ImportFiles\DEL" & Format(Now, "yymmddhhmm") & ".txt" For Output As #intO2
Set O2DB = CurrentDb()
Set rstO2 = O2DB.OpenRecordset("O2Export", dbOpenForwardOnly)

With rstO2
Print #intO2, "IBO" & "|" & "S" & ![LoadRef] & "|" & ![Sver]
Print #intO2, "IBD" & "|" & ![O2PO] & "|" & ![O2ProdC] & "|" & ![CountOfIMEI] & "|" & "U" & "|" & "S" & ![LoadRef] & "|"
Do While Not .EOF
Print #intO2, "SRN" & "|" & ![O2PO] & "|" & "S" & ![LoadRef] & "|" & ![IMEI] & "|||" & ![O2ProdC]
.MoveNext
Loop

End With

Exit_O2Pipe:

Close #intO2
rstO2.Close
Set rstO2 = Nothing

Exit Function

Err_ExpO2Pipe:
MsgBox "Error on Output Verify Data " & Err.Number & ": " & Err.Description
GoTo Exit_O2Pipe

End Function

My problem now is I need to limit the output to a matching citieria of example LoadOut =forms!SelectFrm!LoadOutRef. This works pertfectly in the Query but when running the output code errors as 3601 Too few parameters. Expected 1. I have searched all over for an answer but cannot resolve. It seems a lot of people have come across this in different ways but it seems that when a citeria is applied the code fails, Any suggestions would be greatfully received
Steve

 

[gemma-the-husky]

I didnt realise you could write comma separated values like that!


i thought you had to do something like this, which may help you anyway


const pipe = "|"

mystrg = field1 & pipe & field2 & pipe & field3

write #chan mystrg

 

[SteveE]

Thanks Dave,
I discovered te methord by trial and error and it works great I tested on a 3200 line file with 35 orders and it works 100%. Im not sure if if I am doing it the "correct" way but it works, and I cannot as yet produce any errors.
the full code in case your are interested is below:

Function ExportO2Pipe()
On Error Resume Next

Dim O2DB As DAO.Database
Dim rstO2 As DAO.Recordset
Dim intO2 As Integer
Dim Order As String

intO2 = FreeFile()
Open "C:\ImportFiles\DEL" & Format(Now, "yymmddhhmm") & ".txt" For Output As #intO2

Set O2DB = CurrentDb()
Set rstO2 = O2DB.OpenRecordset("O2Export", dbOpenForwardOnly)

LStart:
Order = rstO2!DOrder

If Order = rstO2!DOrder Then

With rstO2
Print #intO2, "IBO" & "|" & "S" & ![DOrder] & "|" & ![Sver]
Print #intO2, "IBD" & "|" & ![O2PO] & "|" & ![O2ProdC] & "|" & ![CountOfIMEI] & "|" & "U" & "|" & "S" & ![DOrder] & "|"
Do While Not .EOF
Print #intO2, "SRN" & "|" & ![O2PO] & "|" & "S" & ![DOrder] & "|" & ![IMEI] & "|||" & ![O2ProdC]
.MoveNext
If Order <> rstO2!DOrder Then
GoTo LStart
End If

Loop

End With
End If

Exit_O2Pipe:

Close #intO2
rstO2.Close
Set rstO2 = Nothing
Exit Function

Err_ExpO2Pipe:
MsgBox "Error on Output Verify Data " & Err.Number & ": " & Err.Description
GoTo Exit_O2Pipe
End Function

Thanks again Dave it was your opening reply that set me on the right path !
Steve