Results 1 to 9 of 9
  1. #1
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51

    Replace a specific space

    I need to replace a specific space(s) in a string. I have a Access (v 2302) select query called sel_tblPIPICK00. The field called SpecInstrDesc has records like this:
    PRL 3435 COLOR COORD
    PHT 17102 BLACK RED
    PEN 78744 TOP LEFT



    I need the first space(s) from left let removed so it looks like this:
    PRL3435 COLOR COORD
    PHT17102 BLACK RED
    PEN78744 TOP LEFT

    I tried Replace function, but it removes all the spaces. Not what I want.

    Please help.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Perhaps look at the Replace function a little closer?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: remove4th
    ' Purpose: Routine to remove the 4th char (space in sample) of a string
    ' Procedure Kind: Function
    ' Procedure Access: Public
    ' Parameter sin (String): Incoming string
    ' Return Type: String
    ' Author: Jack
    ' Date: 21-Apr-23
    ' Relates to: https://www.accessforums.net/showthread.php?t=87978
    ' ----------------------------------------------------------------
    Function remove4th(sIn As String) As String
        remove4th = Left(sIn, 3) & Mid(sIn, 5)
    End Function
    Sample function call

    Code:
    Sub testw()
    Dim sin As String
    sin = "PRL 3435 COLOR COORD"
    Debug.Print remove4th(sin)
    End Sub
    Result:

    PRL3435 COLOR COORD

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The solution depends on the consistency of the space count. The way I read "replace a specific space(s)" there could be more than 1. If there was in the op samples the forum would have stripped the extra ones out. To avoid forum mucking up my spacing, indentation, etc. I use vba tags. Maybe quote tags would work also.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Could use Trim() and any number of spaces will be handled.

    Left(sIn, 3) & Trim(Mid(sIn, 4))

    Critical is consistency of first 3 characters.
    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.

  6. #6
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Thanks for your replies Orange, Micro and June7. The leading three alpha characters are random (ex. PRL, PHT, PEN). But it is always three alpha characters. Then it is followed up with 1 to as many as 5 spaces (that I've seen so far, could possibly be more). Unfortunately the number of spaces are not static.

    Orange's suggestion works good. But in instances of 2 or more spaces, it only removes the first space. Any suggestions?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    See post #5.
    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.

  8. #8
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Thanks June7! That did the trick! Things are hectic here and I thought I tried your suggestion. Sorry about that.

    Thanks to June7, Orange, and Micron for all your help.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Can be done with expression in query or textbox. VBA not required.
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replace ` with ' in all data in a specific field
    By Khalil Handal in forum Queries
    Replies: 4
    Last Post: 02-09-2022, 02:21 AM
  2. Replies: 13
    Last Post: 08-14-2021, 04:53 AM
  3. Replies: 13
    Last Post: 03-22-2019, 04:09 PM
  4. Replies: 3
    Last Post: 05-13-2018, 12:59 AM
  5. Replies: 3
    Last Post: 06-07-2012, 07:05 AM

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