SIMPLE: replace carraige returns in memo field for export to flat file 1

[AndyTilia]

Hi-

I have a table that I need to distribute to people who do not have MS Access, so I am exporting it as a comma-delimited text file. Pretty straightforward, EXCEPT for the fact that one of the fields is of type MEMO. There are numerous records that have carraige returns in that MEMO field --- but they look like wet elephant turds when you export them the text file (ie. the carraige return is exported too, which breaks up the text in that memo field so that it dribbles all over the page, breaking with every CR, causing the records to not be read properly into MS Excel, for example.)

Anyone have thoughts on a solution?? I was imagining some code that, on export, searched through this memo field, replacing all vbCR with something like " - "; wouldn't necessarily look pretty but that's fine, as long as all the text is there and it reads properly into Excel (or wherever one might want to read it).

Thanks in advance,
Andy

 

[scriverb]

Copy and Paste this function into a database module.

Public Function ReplaceEmbedded(CharToReplace As String, SearchString As String, ReplacementChar As String)
Dim i As Integer, s As String, ss As Integer
ss = Len(SearchString)
For i = 1 To ss
If InStr(CharToReplace, Mid$(SearchString, i, 1)) = 0 Then
s = s & Mid$(SearchString, i, 1)
Else
s = s & ReplacementChar
i = i + Len(CharToReplace)
End If
Next i
ReplaceEmbedded = s
End Function

Then make a call to the Function like the following. You will have to adjust this call to the table and memo field names.

Dim db As Database
Dim rs As Recordset
Dim x As String
Dim vSearchFor As String
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTablename", dbOpenDynaset)
rs.MoveFirst
Do
vSearchFor = Chr(13) 'this is the carriage return
x = ReplaceEmbedded(vSearchFor, rs("Memofield&quot, " - &quot
rs.edit
rs("memoField&quot = x
rs.update
rs.movenext
loop until rs.eof
rs.close
db.close

There are three required parameters in the call to the function.
1. Characters being searched for. In this instance chr(13) or the carriage return/paragraph marker.
2. String or memofield data that you want to perform the search and replace on.
3. The replacement characters. After the carriage return is encountered replace it with these characters. I used " - " to give it a little more spacing in the final string. This can be changed to anything that you want.

I would test this on a test database before I went live. I tested it here and it seems to work as you requested.

Bob Scriver

 

[AndyTilia]

Bob- Thanks very much. This works well except for one problem: it seems that a number of the memo fields have a different kind of carraige return that shows up in the textbox as a lower-case "i" followed immediately by a skinny bar. It looks approximately like this: i|

This character combination is not being read by Access as a carraige return (in the textbox, it is just that pair of characters NOT immediately followed by a new line). HOWEVER, when it is exported to text, the bar following the "i" IS read as a carraige return.

They arent being picked up by the chr(13). These records, by the way, are ones that I imported from an old government database. All of the other records' CRs *are* picked up very nicely by your function.

Do you know what would be the "chr(13)"-style equivalent of these skinny bars, so I can search & replace those as well?

Thanks again for your help.

Andy

 

[scriverb]

The way that I designed the Function you can change the SearchFor value. Just change that statement to look for the ";|" combination. If you have to cut and paste the two characters into the statement. The Function will search for those characters and replace replace them with the replacement characters.

vSearchFor = "i|"
x = ReplaceEmbedded(vSearchFor, rs("Memofield&quot, " - &quot

Let me know how that works.

Bob Scriver

 

[AndyTilia]

Bob- When I did this the VBA editor read the skinny bar as a carraige return, so your example above turns out looking like this:

vSearchFor = "i
"
x = ReplaceEmbedded....

Argh. A little bit of exploring and experimenting though has led me to chr(10) as the representative of the skinny bar.

So: Now your example looks like this:

vSearchFor = "i" & chr(10)
x = ReplaceEmbedded....

And that works well! Problem Solved. Q.E.D. etc.

Thank you Bob.

Andy

 

[scriverb]

Andy, I am glad that it all worked for you.

Good luck with your project. Thanks for the Star.

Bob Scriver