Need to TRIM imbedded spaces- Is there a function 1

[Dylan]

I have a string field with embedded spaces that I would like to eliminate the spaces from. The spaces are not always in the same position:<br><br>Example<br><br><b>String input:</b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;723 245 777<br><br><b>I would like to return as:</b>&nbsp;&nbsp;723245777<br><br>The normal trim function only eliminates leading and trainling spaces as I understand it.<br>Thanks for any help ...

 

[elizabeth]

I don't know of a built-in Access function but here's a clunky way I've done it in a form with a button. You could create a similar function and call it from an update query.<br><br>Private Function cmdRemoveBlanks_Click()<br>Dim strWholeString As String<br>Dim intLocation As Integer<br><br>strWholeString = Me.uppername<br>&nbsp;&nbsp;&nbsp;&nbsp;If Not InStr(1, strWholeString, &quot; &quot Then ' look out for nulls<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do While InStr(1, strWholeString, &quot; &quot &gt; 0 ' 0 means string not found<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;intLocation = InStr(1, strWholeString, &quot; &quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strWholeString = Left(strWholeString, intLocation - 1) & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Mid(strWholeString, intLocation + 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Me.uppername = strWholeString<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub

 

[Cornerstone]

This works beautifully<br><br>Function TrimSpace(strInput As String) As String<br>&nbsp;&nbsp;&nbsp;&nbsp;' This procedure trims extra space from any part of<br>&nbsp;&nbsp;&nbsp;&nbsp;' a string.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim astrInput()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim astrText()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strElement&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim lngCount&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Long<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim lngIncr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;As Long<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Split passed-in string.<br>&nbsp;&nbsp;&nbsp;&nbsp;astrInput = Split(strInput)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Resize second array to be same size.<br>&nbsp;&nbsp;&nbsp;&nbsp;ReDim astrText(UBound(astrInput))<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Initialize counter variable for second array.<br>&nbsp;&nbsp;&nbsp;&nbsp;lngIncr = LBound(astrInput)<br>&nbsp;&nbsp;&nbsp;&nbsp;' Loop through split array, looking for<br>&nbsp;&nbsp;&nbsp;&nbsp;' non-zero-length strings.<br>&nbsp;&nbsp;&nbsp;&nbsp;For lngCount = LBound(astrInput) To UBound(astrInput)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strElement = astrInput(lngCount)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Len(strElement) &gt; 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Store in second array.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;astrText(lngIncr) = strElement<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;lngIncr = lngIncr + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Resize new array.<br>&nbsp;&nbsp;&nbsp;&nbsp;ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;' Join new array to return string.<br>&nbsp;&nbsp;&nbsp;&nbsp;TrimSpace = Join(astrText, &quot;&quot '2nd arg is empty string<br>End Function<br>

 

[JimHorton]

Howabout Replace(&quot;your string with spaces&quot;,&quot; &quot;,&quot;&quot=<br>yourstringwithspaces<br>--Jim