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://accessmvp.com/theDBguy If InStr(str, " ") > 0 Then str = TrimSpaces(Replace(str, " ", " ")) End If TrimSpaces = str End Function
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
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
- Generate GUIDs
- Backup and Compact BE
- Get E-mail Address from AD
- Get Network Username
- Leigh's Generic Recordset
- Trim Inner Spaces
- Get Subform Control Name
- The Opposite of DSum()
- Concatenate Records
- Get Network Domain Name
- Get Computer Name
- Get BE Info
- Execute Action Queries
- Extract Email Address