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

    Re: Double Spaces (A2K)

    Sample function to change any double (or longer) embedded spaces (including tabs) to single space, and to trim any leading/trailing spaces:

    <code>Function TrimAll(ByVal strInput As String) As String</code>

    <pre> Const SPACE_SINGLE = " "
    Const SPACE_DOUBLE = " "</pre>

    <code> ' Replace any tabs with space, trim leading/trailing spaces:</code>
    <code> strInput = Replace(Trim$(strInput), vbTab, SPACE_SINGLE, , , vbBinaryCompare)</code>

    <code> 'Replace any double-spaces with single-space:</code>
    <code> Do Until InStr(strInput, SPACE_DOUBLE) = 0</code>
    <code> strInput = Replace(strInput, SPACE_DOUBLE, SPACE_SINGLE, , , vbBinaryCompare)</code>
    <code> Loop</code>

    <code> TrimAll = strInput</code>

    <code>End Function</code>

    HTH

  2. #2
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Double Spaces (A2K)

    Mark, thanks kindly for the quick reply. Having no personal shame whatsoever, I have two questions:
    1. Where exactly would I apply the field name "M_Name" in your equation, and
    2. How exactly would I apply it to my table/query currently holding that name, so that I can clean it up?
    In spite of the apparent simplicity of these questions, I actually do know some stuff ...... just not this particular item. Thanks again in advance..
    Cheers,
    Andy

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Double Spaces (A2K)

    Hi Andy,
    it's a function so TrimAll(M_Name) will return M_Name but trimmed. If you want to convert your table data permenantly then use an update query to update M_Name to TrimAll(M_Name) & it's done!

    Ian

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

    Re: Double Spaces (A2K)

    As previously reply noted, an Update query would be simplest way to update field. First, copy function previously posted to a standard (not form or class) code module. Note function declared as "Public" so can be called from anywhere in database in proper context. Then create update query. The query SQL should look something like this:

    UPDATE Table1 SET Table1.M_Name = TrimAll([M_Name])
    WHERE (((Table1.M_Name) Is Not Null));

    Replace "Table1" with actual name of table. Note the query criteria specifies field Is Not Null, because function expects a string value. Passing Null value to function will result in error, and update query would fail.

    HTH

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Double Spaces (A2K)

    Edited by HansV - used <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to preserve multiple spaces - see <!help=19>Help 19<!/help>

    I swear I'm going to setup a new logon so that when I have a really dumb question, not everyone will know about it. Anyway, I have a field called M_Name which contains text information such as the following:
    <pre>Adam 1
    Bob 3
    Charlie 4
    </pre>

    All I'm trying to do is get rid of the extra space in Adam and Charlie. Duh!
    Cheers,
    Andy

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Double Spaces (A2K)

    Mark & Ian,

    I thank you both kindly for helping me. Sorry for my delay in responding, but I just came back from doing bad 5 hour imitation of Lance Armstrong. He need never look over his shoulder for me. However, I could of used his help to find my poor, sorry butt which I know is still out there somewhere. Anyway, to the situation at hand, again, thank you both for helping and I'll get right to it. Standing up.

    Andy
    Cheers,
    Andy

Posting Permissions

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