Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing spaces from text entries (Access 2000)

    I think there was a post previously but cannot search for it. Could someone tell me how to remove spaces from a text entry. I have entries such as 47 100000, 1000000000 1 02, 47177777, 1001112222 1, in a field called [SALN] and would like them to remove the spaces so that they will look like 47100000, 1000000000102, 47177777, 1001112221. Each of these are separate records in a field, this is not one field. Trim ([SALN]) will not remove the spaces.

    Thanks in advance for any assistance.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing spaces from text entries (Access 2000)

    In 2000 there is a function Replace() which you can use for this:

    <pre>Replace([SALN]," ","")</pre>


    This formula tells Access to replace the space in the field with an empty string.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing spaces from text entries (Access 2000)

    Thanks

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing spaces from text entries (Access 2000)

    I tried this in a Select Query and got the error listed on my attachment at the top. Am I missing something? I also tried this with the function Substitue which according to the Help files seemed more appropriate for what I was trying. In each case I got this similar error. I also attached a shot of my references in case you saw something here that was missing.

    Thanks.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing spaces from text entries (Access 2000)

    Marc,

    Substitute is an Excel worksheet function; it is not available in Access.

    Some of the new VBA functions introduced in Office 2000 VBA are not available in expressions. I don't have Access 2000 myself, so I can't test whether Replace is one ot them (it does work in Access 2002). As a workaround, you can create a custom function in a standard module:

    Public Function MyReplace(sExpression, sFind, sReplace)
    If IsNull(sExpression) Then
    MyReplace = Null
    Else
    MyReplace = Replace(sExpression, sFind, sReplace)
    End If
    End Function

    Use MyReplace instead of Replace in the query.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing spaces from text entries (Access 2000)

    Worked like a charm!

    Thanks again.

Posting Permissions

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