Removing carriage returns from Memo fields Sandie 01.09.2006 15:29:02 Hi, I need to export data from a memo field to a tab delimited text file. But I need to strip out the carriage returns in the memo field. Any suggestions on how to do this? I don't see the characters in the text file so it's I don't know how to tell if they are gone (I tried the Replace function). Thanks in Advance.

Re: Removing carriage returns from Memo fields "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 01.09.2006 16:31:48 Replace(TextToChange, vbCrLf, " ") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Sandie" <Sandie[ at ]discussions.microsoft.com> wrote in message news:5490CC30-8BF4-402C-8776-A3AAB18F5B3F[ at ]microsoft.com... [Quoted Text] > Hi, > > I need to export data from a memo field to a tab delimited text file. > But I need to strip out the carriage returns in the memo field. Any > suggestions on how to do this? > > I don't see the characters in the text file so it's I don't know how to > tell if they are gone (I tried the Replace function). > > Thanks in Advance. >

Re: Removing carriage returns from Memo fields Sandie 01.09.2006 19:31:02 The constant "vbCRLf" doesn't work in an update query but I wrote up a little bit of code and that took care of it. Thanks! My code is far from good but in case it will someone else... rs.MoveFirst Do With rs If Not IsNull(rs![comments]) Then .Edit ![comments] = Replace(rs![comments], vbCrLf, " ") .Update End If End With rs.MoveNext If rs.EOF Then Exit Do End If Loop "Douglas J. Steele" wrote: [Quoted Text] > Replace(TextToChange, vbCrLf, " ") > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "Sandie" <Sandie[ at ]discussions.microsoft.com> wrote in message > news:5490CC30-8BF4-402C-8776-A3AAB18F5B3F[ at ]microsoft.com... > > Hi, > > > > I need to export data from a memo field to a tab delimited text file. > > But I need to strip out the carriage returns in the memo field. Any > > suggestions on how to do this? > > > > I don't see the characters in the text file so it's I don't know how to > > tell if they are gone (I tried the Replace function). > > > > Thanks in Advance. > > > > >

Re: Removing carriage returns from Memo fields "Ken Snell \(MVP\)" <kthsneisllis9[ at ]ncoomcastt.renaetl> 01.09.2006 19:45:17 Use Chr(13) & Chr(10 in place of vbCrLf in the query. -- Ken Snell <MS ACCESS MVP> "Sandie" <Sandie[ at ]discussions.microsoft.com> wrote in message news:CF5CE88B-E26E-4B20-A840-5D7F97542E6B[ at ]microsoft.com... [Quoted Text] > The constant "vbCRLf" doesn't work in an update query but I wrote up a > little > bit of code and that took care of it. Thanks! > > My code is far from good but in case it will someone else... > > rs.MoveFirst > Do > With rs > If Not IsNull(rs![comments]) Then > .Edit > ![comments] = Replace(rs![comments], vbCrLf, " ") > .Update > End If > End With > > rs.MoveNext > If rs.EOF Then > Exit Do > End If > Loop > > > > "Douglas J. Steele" wrote: > >> Replace(TextToChange, vbCrLf, " ") >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no private e-mails, please) >> >> >> "Sandie" <Sandie[ at ]discussions.microsoft.com> wrote in message >> news:5490CC30-8BF4-402C-8776-A3AAB18F5B3F[ at ]microsoft.com... >> > Hi, >> > >> > I need to export data from a memo field to a tab delimited text file. >> > But I need to strip out the carriage returns in the memo field. Any >> > suggestions on how to do this? >> > >> > I don't see the characters in the text file so it's I don't know how >> > to >> > tell if they are gone (I tried the Replace function). >> > >> > Thanks in Advance. >> > >> >> >>

Re: Removing carriage returns from Memo fields "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 01.09.2006 19:57:30 Yeah, as Ken points out, in SQL, you can't use intrinsic constants such as vbCrLf. You need to use Chr(13) & Chr(10) in that order. Note that it's almost always considerably faster to use an UPDATE statement in SQL than to loop through a recordset. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Sandie" <Sandie[ at ]discussions.microsoft.com> wrote in message news:CF5CE88B-E26E-4B20-A840-5D7F97542E6B[ at ]microsoft.com... [Quoted Text] > The constant "vbCRLf" doesn't work in an update query but I wrote up a > little > bit of code and that took care of it. Thanks! > > My code is far from good but in case it will someone else... > > rs.MoveFirst > Do > With rs > If Not IsNull(rs![comments]) Then > .Edit > ![comments] = Replace(rs![comments], vbCrLf, " ") > .Update > End If > End With > > rs.MoveNext > If rs.EOF Then > Exit Do > End If > Loop > > > > "Douglas J. Steele" wrote: > >> Replace(TextToChange, vbCrLf, " ") >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no private e-mails, please) >> >> >> "Sandie" <Sandie[ at ]discussions.microsoft.com> wrote in message >> news:5490CC30-8BF4-402C-8776-A3AAB18F5B3F[ at ]microsoft.com... >> > Hi, >> > >> > I need to export data from a memo field to a tab delimited text file. >> > But I need to strip out the carriage returns in the memo field. Any >> > suggestions on how to do this? >> > >> > I don't see the characters in the text file so it's I don't know how >> > to >> > tell if they are gone (I tried the Replace function). >> > >> > Thanks in Advance. >> > >> >> >>