Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove empty lines from table (AXP)

    Hello All,
    I have made a make table query to export certain parts of an existing table to a new table. I have a memo field and what I need to do is remove empty lines (carriage returns) from that field. Does anyone know how I can do this?

    Thanks,
    Mark

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Remove empty lines from table (AXP)

    Can you use the Replace function?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Remove empty lines from table (AXP)

    As Patt suggests recommend use Replace function in query expression or function. Example:

    SELECT TABLE1.COMMENTS, Replace(Nz([COMMENTS],""),Chr(13) & Chr(10)," ") AS [NO LINES1], Replace(Nz([COMMENTS],""),Chr(13) & Chr(10) & Chr(13) & Chr(10),Chr(13) & Chr(10)) AS [NO LINES2], ReplaceBlankLines(Nz([COMMENTS],"")) AS [NO LINES3]
    FROM TABLE1;

    In above example, COMMENTS is Memo field in TABLE1. The 1st query expression (NO LINES1) replaces all Carriage Return (Chr(13)) and Line Feed (Chr(10)) (CR/LF) characters with a space, resulting in a single block of text. This did not produce very useful results. 2nd expression (NO LINES2) replaces every double CR/LF with single CR/LF. This eliminates the blank lines where there is a single blank line between text in field. However, this will not work if memo field has more than one blank line between text entries; there will still be blank lines in field. To eliminate all blank lines in this case, you can use a user-defined function (3rd expression in example). Function example:

    Public Function ReplaceBlankLines(ByVal strText As String) As String

    ' Eliminate blank lines in block of text:
    Do
    strText = Replace(strText, vbCrLf & vbCrLf, vbCrLf)
    Loop Until InStr(1, strText, vbCrLf & vbCrLf, vbBinaryCompare) = 0

    ReplaceBlankLines = strText

    End Function

    In testing, this function provided best results. Note that you can't use named constants like vbCrLf in query expression, that's why Chr(13) & Chr(10) are used to represent CR/LF. Also note use of Nz function to avoid errors where memo field is blank (Null).

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •