• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2077
  • Last Modified:

Using Trim Function in Echo remove a blank in the middle of a string

Is there a way using the Trim function in Access to remove a space.  In the following address field which is coming from a packaged field.  It looks like this:
                         Manhasset, NY  11030-1945.  

I would like to remove one of the spaces between NY and 11030-1945 without bringing each field in separately and concatanating them together if possible.
0
morinia
Asked:
morinia
1 Solution
 
Rey Obrero (Capricorn1)Commented:

try

replace([myfield],space(2),space(1))
0
 
peter57rCommented:
You can replace 2 spaces with 1 if that's what you mean - it will look through the whole string though...
replace(fieldname, "  ", " ")
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Is there a way to put this into a query or macro so it can be automated?  I
0
 
Rey Obrero (Capricorn1)Commented:
create an update query

update MyTable
set [Myfield]=replace([myfield],space(2),space(1))
0
 
Patrick MatthewsCommented:
Hello morinia,

The Access and VBA Trim() function only removes leading and trailing spaces.  Excel's TRIM()
function does that as well as remove excess interior spaces, but it would be excessive to
instantiate an Excel object just for that.

cap1 and Peter have you squared away if you only need to worry about two spaces.  If you
might have additional spaces (say, three or more consecutive), then I would add this UDF:



Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True)

    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
    ' whether all instances of the matched string are replaced (True) or just the first instance (False)
   
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
   
    ' If you use this function from Excel, you may substitute range references for all the arguments
   
    Dim RegX As Object
   
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
    End With
   
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
   
    Set RegX = Nothing
   
End Function


and then use it like this:

UPDATE YourTable
SET YourField = RegExpReplace([YourField], " {2,}", " ")


Regards,

Patrick
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now