Access question - blimey, this is hard work.

The revenge of carriage return is at hand.

Errrr... I'm not even sure that I understand your question.

But I've been wrestling with a similar problem in Worksmart (a beastly program if ever I saw one) - what I ended up doing was using a distinct character (@ or # or * but those later ones are tricky as they tend to get misinterpreted as wild cards) instead of a linebreak and then run search and replace once I've got it in the actual format I want - i.e. text

It sounds like you're using forms and VBA, and you can certainly write a text file from VBA. I'm not quite clear on what you need to do with the file, though, so some of this may not be appropriate. Probably too late now anyway; sorry.

Do you really mean just ASCII 13 (old-fashioned Mac-style line break, built-in VB/VBA constant vbCr) and not the standard Windows linebreak 13 10 (constant vbCrLf)? You can export all the fields and records in a table using TransferText, something like this:

DoCmd.TransferText acExportDelim, , "Table1", "accesstest.txt", False

(acExportDelim = comma-delimited text file, the next parameter I never use, table name, output filename, false for don't include the field names)
but when I did it it preserved all the linebreaks but it converted them to CR LF.

But if there's no reason to put it in an intermediate table, then look up the VBA statements Open and Close to open a file for read or write, Print # or Write # to write to the file, and Input # to read from it again.

There's just one annoying thing: do you want the file to be read by other applications or to load it in again in Access? If you use Write # it'll wrap the string in quotation marks, which may not be what you want, but it has the advantage that you can load it into Access again with Input # and it won't stop at the line breaks, it'll load everything between a pair of quotation marks. If you use Print # there won't be any quotation marks, just the string, but Input # will stop at every CR and you won't get it to load them in even though they'll be there in the file, grr.

Here's a quick example which works in Access 2002 (disclaimer: may need tweaking for other versions, Access is a pain like that), but for a real world database with more than one user you might need to think about file locking/sharing (read up on the optional parameters to Open) and sequential filenaming or letting the user choose a filename using the standard Save As dialog box.

Dim lngFileNum As Long
Dim strTest As String
Dim strLoadTest As String

' a test string
strTest = "oh, um" & vbCr & "badgers" & vbCr & "and stuff" & vbCr

' Write the string to a file
lngFileNum = FreeFile ' get the first free filenumber
Open "accesstest2.txt" For Output As lngFileNum ' you might want Append and not Output
Write #lngFileNum, strTest
Close lngFileNum ' don't forget to close the file when you've finished writing to it

' Let's see what was written to the file
lngFileNum = FreeFile
Open "accesstest2.txt" For Input As lngFileNum
Input #lngFileNum, strLoadTest
Close lngFileNum

strLoadTest = Replace(strLoadTest, vbCr, "[ASCII 13]")
MsgBox "Your file said:" & vbCrLf & strLoadTest

End Sub

I think there are newer ways of doing it involving something like TextStream objects or something, too, but I'm afraid I haven't done much Access work for years.

Mammoth dull post and I suspect it was not much help, sorry, but maybe you'll have a better idea what to look for now.

I should probably say that not only have I not done any Access work recently but that it was always small and unimportant databases that mostly hardly ever had more than one user at once, so I am probably in some very bad habits of just throwing stuff together and hoping nobody uses it enough to notice. But... but... oh, anyway. Good luck!

Nice work everyone! I'll check out the write# and print# commands today and see what happens. Cheers y'all!

Thank you all you helpful people. Turns out Print# was the answer, and it's all shiny happy now. Thank you thank you thank you!

(this also gives me a reason to be on ILX< since if I'm asked I can legitimitly say "but they help me with access problems!")