Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122

    Removing leading blank lines in a Memo Field

    So, some users will enter an extra blank line in a Memo field. How can I remove that? e.g.



    ----------------------

    Hello this is a test.
    ----------------------

    Instead of:

    ----------------------
    Hello this is a test
    ----------------------

    Trim([Text]) does not seem to remove that. If it where " Hello this is a test." it will trim it, but not with an new new)

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Possibly this is a CR and LF. Try:

    Mid([fieldname], 3)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Trim([field]) does work.
    unless the 1st field is not a space,


    see what it is by getting the 1st character code:
    asc(left([field],1))

    lookup on ASCII chart

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if it's actually a memo/long text field I'd be surprised either worked. String functions on memo fields tend to truncate after the 255th character. As long as your memo field is no longer than that you should be fine though.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    perryc,

    It would be helpful if we could see the actual data. A few records from the table would be fine. Can you post a copy of your database in zip format?

    If Trim isn't working then, as June suggested, you may have some combination of LF, CR etc.

  6. #6
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    Click image for larger version. 

Name:	Capture.JPG 
Views:	19 
Size:	14.5 KB 
ID:	38682

    This is what the field looks like. As you can see, it is not a leading " " but a new line. I want to remove the top blank spaces.

    Trim([Comments]) does not work. It won't remove the leading spaces.
    Left([Comments]) also does not work either. It won't remove the Empty line.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    is it an rtf field? If so, there will be a whole whack of characters on the left side of the content. Consider that for

    - line wrap here-
    fsfgsgfsdfgd

    in an rtf field the first 30 leading characters, using

    Code:
    Dim n As Integer
    On Error Resume Next
    
    For n = 1 To 30
    Debug.Print n & "  -  " & Asc(Mid(Me.Text0, n)) & "  -  " & Chr(Asc(Mid(Me.Text0, n)))
    Next
    are

    1 - 60 - <
    2 - 100 - d
    3 - 105 - i
    4 - 118 - v
    5 - 62 - >
    6 - 38 - &
    7 - 110 - n
    8 - 98 - b
    9 - 115 - s
    10 - 112 - p
    11 - 59 - ;
    12 - 60 - <
    13 - 47 - /
    14 - 100 - d
    15 - 105 - i
    16 - 118 - v
    17 - 62 - >
    18 - 60 - <
    19 - 100 - d
    20 - 105 - i
    21 - 118 - v
    22 - 62 - >
    23 - 102 - f
    24 - 115 - s
    25 - 102 - f
    26 - 103 - g
    27 - 115 - s
    28 - 103 - g
    29 - 102 - f
    30 - 115 - s

    this shows that there are 22 characters before the first visible value and no carriage return or linefeed characters. Thus I think that rtf vs plain text might be very significant.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You must have read my post before I edited it to use Mid.

    Mid([Comments], 3)

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    perryc,

    You are showing us that the field contains unprintable characters. We need to see the data in your table or form control.

    Here is a query that displays the 1st, 2nd and 3rd (the asc and chr )and the first 20 characters of a memo field "fld" from table "memoTest".

    Code:
    SELECT memoTest.AName
    , memoTest.AnimalId
    , Len([fld]) AS L
    , Asc(Mid([fld],1)) & "  -  " & Chr(Asc(Mid([fld],1))) & Chr(10) & Chr(13) AS Pos1
    , Asc(Mid([fld],2)) & "  -  " & Chr(Asc(Mid([fld],2))) & Chr(10) & Chr(13) AS Pos2
    , Asc(Mid([fld],3)) & "  -  " & Chr(Asc(Mid([fld],3))) AS Pos3, Left([fld],20) AS Strt20
    FROM memoTest;
    Sample data from my table
    AName AnimalId L Pos1 Pos2 Pos3 Strt20
    Spot 1 1294 73 - I
    32 -
    104 - h I have a application
    Jim 2 182 65 - A
    114 - r
    101 - e Are you a Windows 7
    Sam 3 772 78 - N
    111 - o
    116 - t Not long after Steve

    You can adjust the code to use your table and display the beginning of your Comment (memo) field. This will help identify the culprit characters.

  10. #10
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    FYI, Mid does not work. It returns the same Blank. (I did Mid(Text, 1,150)) The record that has the [Enter] - blank line still shows the same blank line. Ty though.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    question, not answered from #7 - Is it Rich Text or not?
    If it is, you will probably have to strip the 1st 17 characters. BIG difference from normal text. Maybe

    Me.txtBoxName = Replace(Me.txtBoxName, Left(Me.txtBoxName , 17), "", , 1)

  12. #12
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    Click image for larger version. 

Name:	Capture.JPG 
Views:	13 
Size:	31.0 KB 
ID:	38702

    Here are the results you are looking for. Not sure where to go from here.

  13. #13
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    Micron, ty as usual. So, by doing so, I lost those that does not have a space or an empty line.

  14. #14
    perryc is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2019
    Posts
    122
    Micron, BTW, this Comments Field is a Plain Text Field. And somehow the first 1-2 character is not Null or " "!!??? This is what I got from running your code:

    1 - 13 -


    2 - 10 -


    3 - 68 - D
    4 - 105 - i
    5 - 100 - d
    6 - 32 -
    7 - 110 - n
    8 - 111 - o
    9 - 116 - t
    10 - 32 -
    11 - 68 - D
    12 - 67 - C
    13 - 32 -
    14 - 119 - w
    15 - 101 - e
    16 - 101 - e
    17 - 107 - k
    18 - 101 - e
    19 - 110 - n
    20 - 100 - d
    21 - 32 -
    22 - 45 - -
    23 - 32 -
    24 - 84 - T
    25 - 65 - A
    26 - 77 - M
    27 - 73 - I
    28 - 75 - K
    29 - 65 - A
    30 - 32 -

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perryc,

    The 13 and 10 in pos1 and pos2 respectively are control characters for "CR carriage return" and "LF Line feed". And that is displayed as a new blank line.

    If you do not want those characters in your data, then you should replace those characters.

    General logic with vba to update a field with the value starting in position 3 to the end of the field.
    Code:
    If (Left(comments,2) = chr(13) & chr(10) Then
    comments = mid(comments,3)
    end if
    I mocked up some data with Chr(13) & Chr(10) in position 1 and 2,
    this query updated the record to remove those 2 characters.
    Code:
    UPDATE memoTest 
    SET memoTest.fld = IIf(Left(fld,2)=Chr(13) & Chr(10),"" & Mid(fld,3),fld);
    You will have to adjust the query to match your table and field names.
    Good luck.
    Last edited by orange; 06-11-2019 at 01:45 PM. Reason: adjusted after testing ---works for me

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Removing lines from a query
    By Big D in forum Queries
    Replies: 1
    Last Post: 08-07-2013, 07:54 AM
  2. Replies: 1
    Last Post: 04-16-2013, 07:57 PM
  3. Replies: 12
    Last Post: 03-03-2013, 07:13 PM
  4. Replies: 3
    Last Post: 12-28-2011, 01:45 PM
  5. Remove lines from Memo field
    By bvallez in forum Programming
    Replies: 5
    Last Post: 08-08-2011, 06:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums