Insert record into a query at specific points

ok...a query...OR do you want to go ahead and create the output file at the same time? It would be easier

Output file is fine or create a table...basically whatever it takes so I can export the results in the proper format and order, so I can import the results into quickbooks.

ok, what's the format and order? we'll do this in one shot...no tables.

can I take the query data and work from that then?

I need to insert  a record with "ENDTRNS" in the first column after each transaction.
TRNS = The start of the main transaction I.E.:
TRNS      DEPOSIT      2/1/2007      ACS      MultiPlaza CCARD      195
SPL = The splits that make up that transaction (the quantity of these can very depending on how many split times make up the main transaction) I.E.:
SPL      DEPOSIT      2/1/2007      BANK FEE                  -4.38
SPL      DEPOSIT      2/1/2007      BANK Deposit            -190.62

Quickbooks needs "ENDTRNS" after each transaction so it knows that its ended. Son the final output nees to be like this:

TRNS      DEPOSIT      2/1/2007      ACS      MultiPlaza CCARD      195
SPL      DEPOSIT      2/1/2007      BANK FEE                  -4.38
SPL      DEPOSIT      2/1/2007      BANK Deposit            -190.62
ENDTRNS

> ... or before each "TRNS" ...

That one is easy. Simply replace TRNS with ENDTRNS<cr/lf>TRNS

In the query, provided the first column is called Action, you could use:

    Code: IIf(Action='TRNS', 'ENDTRNS' & Chr(10) & Chr(13) & 'TRNS', Action)

This would fool the export tool into creating two lines instead of one.

To insert after each group is more complex, and we would need to know more about your current query.

(°v°)

I was thinking an export function here...

Function ExportText1()
Dim rs1 As New dao.Recordset
Dim rs1 As New dao.Recordset
Dim str
Dim strRecord As String
Dim strExpFile As String
strExpFile = "C:\MyOutput.txt"
Set rs1 = CurrentDb.OpenRecordset("select distinct [date] from yourquery;")
If rs1.EOF Then Exit Function
Open strExpFile For Output As #1
rs1.MoveFirst
Do Until rs1.EOF
    Set rs2 = CurrentDb.OpenRecordset("select * from yourquery where [date] = #" & rs1![Date] & "#;")
    If rs2.EOF Then GoTo NextRecord
        For I = 0 To rs2.Fields.count - 1
            strRecord = strRecord & "delimiter" & rs2.Fields(I).Value
        Next I
    Print #1, strRecord
    rs2.MoveNext
    Loop
    rs2.Close
    Set rs2 = Nothing
    Print #1, "ENDTRNS"
NextRecord:
rs1.MoveNext
Loop
Close #1
rs2.Close
Set rs2 = Nothing
End Function


But I don't know the format you want for each field...so this is a first stab
J

IT would help if instead of exporting we sent the results to a tbl instead. the format for each field is text except for the currancy...however doesn't matter since it will all end up as tab delimited.

If you want a query, we will need to see your current query. It's possible to insert the 'ENDTRNS' using a UNION query, but it's import to see how your current query is sorted.
(°v°)

I'm on my laptop at home right now....I'll post the query sql when I get back to the office in the morning...thanks...from a query I can then do what ever I want with the data :)...Cheers!

I think Jeff's solution is best in your case, but we'll see tomorrow.
(°v°)

Great Job jefftwilley there was a small glitch,  I added this at the end of the code so that ENDTRANS got added after the last trasaction:
    rs2.AddNew
    rs2!TRANS = "ENDTRANS"
    rs2.Update

Also I added code to delete the table from te previous creation and create a new blank table for the new formatted data:

Dim MyDatabase As Database
Set MyDatabase = CurrentDb()

If ObjectExists("Tables", "tblTransNew") = True Then
        MyDatabase.TableDefs.Delete "tblTransNew"
        MyDatabase.TableDefs.Refresh
End If

CurrentDb.Execute ("CREATE TABLE [tblTransNew] ([TRANS] TEXT, [TRNSID] TEXT, [TRANSTYPE] TEXT, [DATE] TEXT, [ACCNT] TEXT, [NAME] TEXT, [CLASS] TEXT, [AMOUNT] TEXT, [DOCNUM] TEXT, [MEMO] TEXT, [CLEAR] TEXT);")

Here is the finished code:

Function ExportText1()
Dim rs1 As dao.Recordset
Dim rs2 As dao.Recordset
Dim FirstTrans As Boolean
Dim I As Long
Dim MyDatabase As Database
Set MyDatabase = CurrentDb()

' delete tblTransNew if it exists
If ObjectExists("Tables", "tblTransNew") = True Then
        MyDatabase.TableDefs.Delete "tblTransNew"
        MyDatabase.TableDefs.Refresh
End If

' Create a new tblTransNew
CurrentDb.Execute ("CREATE TABLE [tblTransNew] ([TRANS] TEXT, [TRNSID] TEXT, [TRANSTYPE] TEXT, [DATE] TEXT, [ACCNT] TEXT, [NAME] TEXT, [CLASS] TEXT, [AMOUNT] TEXT, [DOCNUM] TEXT, [MEMO] TEXT, [CLEAR] TEXT);")

FirstTrans = True
Set rs1 = CurrentDb.OpenRecordset("select * from QRY_BacReformat_2;")
Set rs2 = CurrentDb.OpenRecordset("select * from tblTransNew;")
If rs1.EOF Then Exit Function
rs1.MoveFirst
Do Until rs1.EOF
If rs1!TRANS = "TRNS" And FirstTrans Then
    rs2.AddNew
    For I = 0 To rs1.Fields.Count - 1
        rs2.Fields(I) = rs1.Fields(I)
    Next I
    rs2.Update
    FirstTrans = False
Else
    If rs1!TRANS = "TRNS" Then
        rs2.AddNew
        rs2!TRANS = "ENDTRANS"
        rs2.Update
        rs2.AddNew
        For I = 0 To rs1.Fields.Count - 1
            rs2.Fields(I) = rs1.Fields(I)
        Next I
        rs2.Update
    Else
        rs2.AddNew
        For I = 0 To rs1.Fields.Count - 1
            rs2.Fields(I) = rs1.Fields(I)
        Next I
        rs2.Update
    End If
End If
NextRecord:
rs1.MoveNext
Loop
rs1.Close
' adds ENDTRANS after last transaction
    rs2.AddNew
    rs2!TRANS = "ENDTRANS"
    rs2.Update

Set rs1 = Nothing

End Function

always happy to help!
J