CODEWITHSUNDEEP
sqlms-access
excelguy
excelguy

Reputation: 1624

MS Access, Trim trailing spaces from fields

In my table within my access file, I have strings with trailing spaces after each word. The data type is short text. Ie StackOverFlow

Is their some functionality within access design view to eliminate the trailing spaces? Or do I have to go to the sql query level to trim the trailing spaces?

If it is sql, what can I add to my query to trim StackOverFlow to StackOverFlow.

Upvotes: 0

Views: 2016

Answers (3)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

As a general rule, and data input from forms or editing will automatic trim trailing blanks. However, if you import data, then yes, trailing blanks can occur.

So, you need to clean out existing blanks, but after that, then no trailing blanks should occur.

You could in a standard code module use this routine to clean up a given table.

Sub CleanTable(strTableName As String)

     Dim rst        As DAO.Recordset
     Dim MyField    As DAO.Field
     
     Set rst = CurrentDb.OpenRecordset(strTableName)
     
     Debug.Print "clean table = " & strTableName
     Do While rst.EOF = False
     
        rst.Edit
        For Each MyField In rst.Fields
        
           If MyField.Type = dbMemo Or MyField.Type = dbText Then
              rst(MyField.Name) = Trim(rst(MyField.Name))
           End If
        Next MyField
        
      rst.Update
      rst.MoveNext
     Loop
     
     rst.Close
     
     Beep
     Debug.Print "done clean table"
     
End Sub

So from code, or even debug (immediate) window, you can then go:

CleanTable"tblHotels"

However, once done, then as noted, trailing blanks should NOT re-occur. If they are then additional details about a import from Excel, csv, or dbase or some other data source must be at play and involves details here that you not shared.

And of course, make a backup before you run above types of code.

Upvotes: 0

Gustav
Gustav

Reputation: 55831

Use RTrim:

Select RTrim([YourField]) As CleanText From YourTable

Upvotes: 2

Marko Ivkovic
Marko Ivkovic

Reputation: 1290

Try with InStr to find on what position is the end of string/first space, and just take all on left side.

SELECT Left("StackOverFlow           ", InStr("StackOverFlow           ", " ") - 1);

Upvotes: 1

Related Questions