Here Is An Example Of The VBA Trim Function

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

Offsite Related Information:

“Go Fund Me” Page


($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Facebooktwitterredditpinterestlinkedinmailby feather