Code Snippets

In all the times I have spent participating in forum discussions about all sorts of users' questions, I have learnt a great deal from all the participants.

I have decided to post some of the sample codes that I found users were asking for almost all the time to help me keep them in one place and to make it easier for me to share them again and again.

Hope you find them useful.



Trim Inner Spaces

The Trim() function can be used to remove the leading and trailing spaces from a string; but sometimes, there might be a need to trim the inner spaces too. This function uses recursion to change all double-spaces in a string into a single space character.

Public Function TrimSpaces(str As String) As String
'Converts all double spaces into a single space character.
'4/7/2013
'Source: http://www.accessmvp.com/thedbguy

If InStr(str, "  ") > 0 Then
    str = TrimSpaces(Replace(str, "  ", " "))
End If

TrimSpaces = str

End Function

Update

A fellow MVP (BananaRepublic) pointed out that using a recursive function could result in a "stack overflow" error if the function is called too many times, i.e., there's too many double-spaces in the string. Fellow MVP, Brent Spaulding (datAdrenaline) offers the following alternative using a loop instead.

Public Function RemoveExtraSpaces(ByVal strText As String) As String
   
   Do Until InStr(1, strText, "  ") = 0
       strText = Replace(strText, "  ", " ", 1)
   Loop
   
   RemoveExtraSpaces = Trim(strText)

End Function

More Update

 MVP, Leigh Purvis also offers the following alternative which uses an SQL compliant method.

Function fReplaceToOne(strTest As String, Optional strChar As String = " ")
    
   fReplaceToOne = Replace(Replace(Replace(Trim(strTest), strChar & strChar, _
   		strChar & Chr(7)), Chr(7) & strChar, ""), Chr(7), "")
    
End Function