Text export formatting problem (1 Viewer)

[danly]

I have a report that I want to export to a text file to create an address list that my users can look up using Notepad (rather than opening Access every time).

Problem is, when I export it, it adds all kinds of extra spaces between lines and records. This necessitates me opening the file in a word processor and cleaning it up.

Has anyone found the secret of creating reports that export nicely formatted text files?

Thanks in advance,

Dan Lee

 

[S]

dim db as database, rec as recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("qq", DB_OPEN_DYNASET) 'QQ = TableName
rec.MoveFirst
dim Inte as Integer, que as string
Do Until rec.EOF
Inte = 6
que = rec![firstfield]
Inte = 6 - Len(que)
rec.LockEdits = False
rec.Edit
rec![Firstfield] = que & String((Inte), 32) 'Adds spaces until 6 characters
rec.Update
rec.MoveNext
Loop 'do that for all records
rec.MoveFirst
Do Until rec.EOF
Inte = 50
que = rec![secondField]
Inte = 50 - Len(que)
rec.LockEdits = False
rec.Edit
rec![secondField] = que & String((Inte), 32)
rec.Update
rec.MoveNext
Loop
''''''
Using this code each record is formatted as the same way.
Hope this helps you, S.

 

[dbertanjoli]

I was waiting for someting like this for a too long. Could you please tell me where I am suppose to paste this code in my report (which contains 12 subreports)

Thank you,

Deb

 

[Pat Hartman]

An easier way to do this is with a query and format() functions for each column.

Select Format(txtFld1,"@@@@@") AS 5CharTxt, Format(dtFld1,"mm/dd/yyyy") AS StdDate, Format(numFld1,"00000.00") AS 5And2NumWithDecimal
From YourTable;

Look up Format function in Help. Click on "see also". In that list you will see at the bottom entries for user defined formats. My example shows one of each. Look to help for further explainations.