locked
Trim Function RRS feed

  • Question

  • I have a field in a table that after import it shows like (F45- Antony             Persi), if I cpy and past in excel and use the trim function it show as F46- Antony Pepsi, but when i try to add the trim function in access the field display all the empty spaces on the middle. Expr1: Trim([Value Added Queue Table]![Sales Person]). Any help?

    Fernando Salgueiro

    Monday, May 1, 2017 2:29 PM

Answers

  • The Trim() function in Access only trims spaces at the start and end of a string. Replace() would work except for the fact that you seem to need to retain one space between the first and last name. You would have to write a VBA user-defined function to do the work. 

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, May 1, 2017 2:47 PM
  • Hi FSalgueiro,

    I try to create a table and insert data like yours.

    then I use query like below.

    SELECT replace(cname,"  ","") from data;
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by FSalgueiro Tuesday, May 2, 2017 8:08 PM
    Tuesday, May 2, 2017 3:08 AM

All replies

  • The Trim() function in Access only trims spaces at the start and end of a string. Replace() would work except for the fact that you seem to need to retain one space between the first and last name. You would have to write a VBA user-defined function to do the work. 

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, May 1, 2017 2:47 PM
  • The worksheet function TRIM in Excel "removes all spaces from text except for single spaces between words", according to the documentation.

    Access uses the VBA function Trim, which only removes leading and trailing spaces from text.

    If you want to emulate the Excel function in Access, you can copy the following function into a module in the Visual Basic Editor:

    Function TrimXL(s As Variant) As Variant
        On Error GoTo ErrHandler
        With CreateObject("VBScript.RegExp")
            .Pattern = " {2,}"
            .Global = True
            TrimXL = .Replace(Trim(s), " ")
        End With
        Exit Function
    ErrHandler:
        TrimXL = Null
    End Function

    You can then use (for example):

    TrimmedText: TrimXL([TextField])


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, May 1, 2017 3:15 PM
  • Hi FSalgueiro,

    I try to create a table and insert data like yours.

    then I use query like below.

    SELECT replace(cname,"  ","") from data;
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by FSalgueiro Tuesday, May 2, 2017 8:08 PM
    Tuesday, May 2, 2017 3:08 AM
  • Deepak, if you have 2 (or 4 or 6 or 8...) spaces between Antony and Persi, you end up with AntonyPersi. The desired result is Antony Persi.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, May 2, 2017 5:58 AM
  • Hi Hans Vogelaar MVP,

    user can use code below to get proper output.

    Sub demo()
        Dim cdb As DAO.Database
        Set cdb = CurrentDb
        Do While DCount("cname", "data", "cname LIKE ""*  *""") > 0
            cdb.Execute "UPDATE data SET cname = Replace(cname,""  "","" "")"
        Loop
        Set cdb = Nothing
    End Sub
    

    Output:

    this is just sample code for example, user can modify the code as per his requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 2, 2017 8:51 AM
  • That would work, but it would be slow...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, May 2, 2017 3:25 PM
  • If you use replace in this case you need to replace double space with a single space and then repeat until there are not more double spaces.

      Replace([YourField], "  ", " ")


    Build a little, test a little

    Tuesday, May 2, 2017 5:19 PM