Trim all text fields in MS Access database

I recently had a need to trim fields in a table in one of the databases I use. Borrowing code from a newsgroup post, I was able to come up with VBA code to trim all the text fields in all the tables in a database (2003 MDB).

The following code will loop through every table in your database and trim all the text fields. Now all those comparison queries will work, phew! :)

Sub TrimAllTextFieldsAllTables()
' http://groups.google.com/group/comp.databases.ms-access/browse_thread/thread/deeb37ca986ad9ff/7698e4f77f31120c
  Dim db As DAO.Database
  Dim tbls As DAO.TableDefs
  Dim tbl As DAO.TableDef
  Dim thisTable As DAO.TableDef
  Dim SQLString As String
  Dim flds As DAO.Fields
  Dim fld As DAO.Field

  Set db = CurrentDb
  Set tbls = db.TableDefs

  ' loop through each appropriate table (i.e. no linked or system tables)
  For Each tbl In tbls
    If tbl.Attributes = 0 Then
      Set thisTable = tbl
      ' grab all fields
      Set flds = thisTable.Fields

      SQLString = "UPDATE [" & tbl.Name & "] SET "

      ' if field is text, create SQL string to trim it
      For Each fld In flds
        If fld.Type = dbText Then
          SQLString = SQLString & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
        End If
      Next fld

      SQLString = Left(SQLString, Len(SQLString) - 1) & ";"
      Debug.Print SQLString
      ' execute update statement on table
      db.Execute SQLString, dbFailOnError

    End If
  Next tbl

End Sub
About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 1 Comment(s) on Trim all text fields in MS Access database:

  1. There is no FOR EVERY RECORD???

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel