Text file with One line Spacing

  • Thread starter Thread starter Ken Chan
  • Start date Start date
K

Ken Chan

hi,

I need to convert Access table to text, but my accounting
software requires a blank new line for next new record.

Any sample command to transfer record to text and insert a
Carriage Return to create blank line???

Ken Chan
Singapore
 
Ken said:
I need to convert Access table to text, but my accounting
software requires a blank new line for next new record.

Any sample command to transfer record to text and insert a
Carriage Return to create blank line???
Click to expand...


You could try creating a query that select all the table
fields plus a constant field with the newline string.

SELECT table.*, Chr(13) & Chr(10) As NL
From Table

That might work depending on how you have the export
specification set up??
 
Working! Text file looks OK.

Almost written a VB for Word, now it is must easier to
stay in Access.

Will try on my Accounting software soon.

Thanks : )
 
MYOB accounting system requires items in same Invoices
with no line spacing, new invoices with one line spacing.
Is there a way to add in IF statement so that it will put
in "Carriage Return" for next new invoices no??

My statements,
SELECT INVOICE.CUSTOMER_ID, INVOICE.INVOICE_NO, Chr(13) &
Chr(10) AS NL
FROM INVOICE INNER JOIN [INVOICE-DETAIL] ON
INVOICE.INVOICE_NO = [INVOICE-DETAIL].INVOICE_NO;

Or I have to use VB+ADO to compile all data in Word doc
and export as txt?? Any sample script?

Thanks Again,
Ken Chan
 
Ken said:
MYOB accounting system requires items in same Invoices
with no line spacing, new invoices with one line spacing.
Is there a way to add in IF statement so that it will put
in "Carriage Return" for next new invoices no??

My statements,
SELECT INVOICE.CUSTOMER_ID, INVOICE.INVOICE_NO, Chr(13) &
Chr(10) AS NL
FROM INVOICE INNER JOIN [INVOICE-DETAIL] ON
INVOICE.INVOICE_NO = [INVOICE-DETAIL].INVOICE_NO;

Or I have to use VB+ADO to compile all data in Word doc
and export as txt?? Any sample script?
Click to expand...


A query doesn't keep track of when a value changes, so No,
you can't use an IIf in the above to do what you want.

I suppose you can resort to using Word if that's what you
feel comfortable with, but I would use VBA code (behind a
form button) to open a recordset on your original query,
loop through the selected records and then use the File I/O
statements to write the data directly to the text file.
Check out these areas in Help to see if you can work out the
basics of what you need and come on back with more specific
questions if you need additional assistance.
 
hi Marsh,
I am not so familiar with File I/O.

I tried writing File I/O below, "Invalid Procedure" for
RM.Fields(1) and realize that WRITELINE cannot write data
if it contains “?” because of Chinese characters. Is it
right? That means I need to use Word, right?

Dim dat As Variant

Dim RM As ADODB.Recordset

Set RM = New ADODB.Recordset
RM.ActiveConnection = CurrentProject.Connection
RM.CursorType = adOpenKeyset
RM.LockType = adLockOptimistic

'Open Query
RM.Open "MYOBInvoice", , , , adCmdTable

Dim fs As Object
Dim strTxtLine, strTempFileName As String

'text command-------------------
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("d:\testfile.txt", True)
a.WriteLine ("CoName Invoice# Date
CustomerPO Item_Number Total Inc-Tax_Total")

dat = RM.Fields(1)

While Not RM.EOF



If Not RM.Fields(0) = dat Then
a.WriteLine Chr(13)
a.WriteLine RM.Fields(0) & Chr(32) & RM.Fields
(1) & Chr(32)
Else
a.WriteLine RM.Fields(0) & Chr(32) & RM.Fields
(1) & Chr(32)
End If
RM.MoveNext
dat = RM.Fields(1)
Wend

MsgBox ("Done")
a.Close
Any suggestion?

Thanks

-----Original Message-----
Ken said:
MYOB accounting system requires items in same Invoices
with no line spacing, new invoices with one line spacing.
Is there a way to add in IF statement so that it will put
in "Carriage Return" for next new invoices no??

My statements,
SELECT INVOICE.CUSTOMER_ID, INVOICE.INVOICE_NO, Chr(13) &
Chr(10) AS NL
FROM INVOICE INNER JOIN [INVOICE-DETAIL] ON
INVOICE.INVOICE_NO = [INVOICE-DETAIL].INVOICE_NO;

Or I have to use VB+ADO to compile all data in Word doc
and export as txt?? Any sample script?
Click to expand...


A query doesn't keep track of when a value changes, so No,
you can't use an IIf in the above to do what you want.

I suppose you can resort to using Word if that's what you
feel comfortable with, but I would use VBA code (behind a
form button) to open a recordset on your original query,
loop through the selected records and then use the File I/O
statements to write the data directly to the text file.
Check out these areas in Help to see if you can work out the
basics of what you need and come on back with more specific
questions if you need additional assistance.
Click to expand...
 
Ken said:
hi Marsh,
I am not so familiar with File I/O.

I tried writing File I/O below, "Invalid Procedure" for
RM.Fields(1) and realize that WRITELINE cannot write data
if it contains “?” because of Chinese characters. Is it
right? That means I need to use Word, right?

Dim dat As Variant

Dim RM As ADODB.Recordset

Set RM = New ADODB.Recordset
RM.ActiveConnection = CurrentProject.Connection
RM.CursorType = adOpenKeyset
RM.LockType = adLockOptimistic

'Open Query
RM.Open "MYOBInvoice", , , , adCmdTable

Dim fs As Object
Dim strTxtLine, strTempFileName As String

'text command-------------------
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("d:\testfile.txt", True)
a.WriteLine ("CoName Invoice# Date
CustomerPO Item_Number Total Inc-Tax_Total")

dat = RM.Fields(1)

While Not RM.EOF



If Not RM.Fields(0) = dat Then
a.WriteLine Chr(13)
a.WriteLine RM.Fields(0) & Chr(32) & RM.Fields
(1) & Chr(32)
Else
a.WriteLine RM.Fields(0) & Chr(32) & RM.Fields
(1) & Chr(32)
End If
RM.MoveNext
dat = RM.Fields(1)
Wend

MsgBox ("Done")
a.Close
Any suggestion?
Click to expand...


Sorry Ken, but I can't Help you with this code. I don't use
either ADO nor the FileSystem object. I don't see any
reason why you shouldn't be able to get where you want to go
with this, I'm just not the right person to help you get
there.

The File I/O I was referring to is the Access builtin
statements Open #, Print #, Write #, etc. I prefer to work
without bringing extra libraries into a problem unless I
can't find an Access feature to do it with.
 
I re-write to the File I/O as you suggested. It works and
is much faster.

Thanks for the advise.

Cheers
Ken ; )
 
Ken said:
I re-write to the File I/O as you suggested. It works and
is much faster.
Click to expand...


Thanks for posting back, Ken. It's good to know that you
got better performance from the Access File I/O statements
than you did using the File System object.
 
Back
Top