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.
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.
You can replace 2 spaces with 1 if that's what you mean - it will look through the whole string though...
replace(fieldname, " ", " ")
replace(fieldname, " ", " ")
ASKER
Is there a way to put this into a query or macro so it can be automated? I
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Reg
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],
Regards,
Patrick
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.Reg
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],
Regards,
Patrick
try
replace([myfield],space(2)