The “Trim” function is useful in eliminating the blank spaces in a string
normally caused by some data entry error.
Why do you want to do this? Well because a sorting error can happen if you are trying to
sort ” Dan, “Fred”, and “Ann”. ” Dan” will show up on top before “Ann” because of that space.
I normally use the “Trim” function before the “Len” function (which checks the length of the string in VBA), otherwise I can get an incorrect string length.
In this example we’ll read the values of a column and we’ll clean up the string that
has some stray spaces.
Sub VBATrimFunction() Dim rst As Object Dim strFname As String Dim intLen As Integer Set rst = CurrentDb.OpenRecordset("SELECT FirstName FROM tblNames ORDER BY ID") Do Until rst.EOF strFname = rst.Fields("FirstName") intLen = Len(strFname) Debug.Print "Name Before Trim: " & strFname Debug.Print "Length Before Trim: " & intLen strFname = Trim(strFname) intLen = Len(strFname) Debug.Print "Name After Trim: " & strFname Debug.Print "Length After Trim: " & intLen rst.MoveNext Loop rst.Close Set rst = Nothing End Sub
Let me know if you have any questions, or click here for related posts