dcsimg
Results 1 to 4 of 4

Thread: [RESOLVED] Is there any code to remove leading & trailing spaces from MS Access database table?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    273

    Resolved [RESOLVED] Is there any code to remove leading & trailing spaces from MS Access database table?

    I am using MS Access database for entering data. One Table (tblSales) which contains 45 Fields with 2400 + records.
    This Table fields have the following properties:

    AutoNumber: Long Integer
    Text: Text (Filed Size: 255; Format: @)
    Number: Long Integer
    Date/Time: Medium Date
    Currency: Standard

    Randomly, there are some rows which contain text/values with some extra spaces (mistakenly it was entered). Checking one by one row with 45 columns is difficult. So, I would like to know whether is it possible to remove all extra spaces (leading and trailing) from the table rows in one go with code.

  2. #2
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,061

    Re: Is there any code to remove leading & trailing spaces from MS Access database tab

    the TRIM function?

    Code:
    UPDATE MyTable SET MyField=TRIM(MyField)

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    273

    Re: Is there any code to remove leading & trailing spaces from MS Access database tab

    Thanks for the reply. One more query, please. Whether is it possible to remove the extra spaces between words as well? Because I found some data that has space in between words as well.
    Like: " Item No. 22 ( Pairs ) " to be "Item No. 22 (Pairs)"
    Last edited by VS2013; Jan 3rd, 2018 at 01:42 PM.

  4. #4
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,061

    Re: Is there any code to remove leading & trailing spaces from MS Access database tab

    That looks far too complicated as far as logic goes, so I wouldn't even attempt to code for all the scenarios you can come up with using SQL.
    However - If you could code the logic in VBA, you can make a custom user defined function in an Access VBA Module, which can be accessed only within MS Access. Custom UDF's (even some normal functions) are disabled outside of Access due to security issues. So you can't use them outside of access from say VB6 or VB.NET.

    Module.bas
    Code:
    Public Function MyTrim(InParam) As String
        MyTrim = Trim(InParam)
        MyTrim = Replace(MyTrim, "( ", "(")
        MyTrim = Replace(MyTrim, " )", ")")
    End Function
    SQL
    Code:
    UPDATE MyTable SET MyField=MyTrim(MyField)

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width