Loop and Trim Text

Logic

Loop through all the tables, and then loop through all fields of non-system tables. If the field data type is Short Text (Type=10), construct and execute an SQL statement to Trim leading and trailing spaces where the value is not null. If the pBooChangeZLStoNull parameter is True, also change ZLS (zero length string) to Null. You could have this go the other way ...

Count how many non-system tables were looked at, how many fields were short text, how many values were trimmed, and how many values were changed from ZLS to Null.

Note: space character is ASCII 32. Required Space is ASCII 160, and is not trimmed.

BACKUP DATA before running this! then, turn on the Debug window (Ctrl-G), paste LoopTables_TrimText, press ENTER, and watch it go!

Parameters

Optional:

pBooChangeZLStoNull is True (default) to also change ZLS to Null

Code

'*************** Code Start *****************************************************
' Purpose  : Loop through tables and fields, and trim text
' Author   : crystal (strive4peace) 
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'--------------------------------------------------------------------------------
'                              LoopTables_TrimText'            BACKUP DATABASE BEFORE YOU RUN THIS!!!!
'--------------------------------------------------------------------------------

Sub LoopTables_TrimText( _
   Optional pBooChangeZLStoNull As Boolean = True _
   )
' s4p 161005, 181207

   On Error GoTo Proc_err

   Dim db As DAO.Database _
      , tdf As DAO.TableDef _
      , oFld As DAO.Field

   Dim sgTimer1 As Single _
      , sgTimeElapse As Single
   
   Dim sSQL As String _
      , sTable As String _
      , sField As String _
      , sMsg As String _
      , nCountTables As Long _
      , nCountFields As Long _
      , nCountTrim As Long _
      , nCountNull As Long _
      , nRecords As Long
      
   sgTimer1 = Timer()

   Set db = CurrentDb
   nCountTables = 0
   nCountFields = 0
   nCountTrim = 0
   nCountNull = 0

   For Each tdf In db.TableDefs
      With tdf
         'skip system tables
         If (.Attributes And dbSystemObject) = 0 Then
            'set status bar with the table name         
            SysCmd acSysCmdSetStatus, .Name         
            sTable = "[" & .Name & "]"
            nCountTables = nCountTables + 1
            Debug.Print "*** " & nCountTables & ". " & sTable
            
            For Each oFld In .Fields
               If oFld.Type = 10 Then
                  sField = "[" & oFld.Name & "]"
                  nCountFields = nCountFields + 1

                  sSQL = "UPDATE " & sTable & " AS t " _
                     & " SET t." & sField & " = Trim(t." & sField & ")" _
                     & " WHERE Not IsNull(t." & sField & ")" _
                     & " AND t." & sField & " <> Trim(t." & sField & ")" _
                     & ";"

                  Debug.Print sSQL
                  db.Execute sSQL
                  nRecords = db.RecordsAffected
                  Debug.Print "----- " & Format(nRecords, "#,##0") & Space(9) & Now()
                  nCountTrim = nCountTrim + nRecords
                  
                  'change ZLS to Null if desired
                  If pBooChangeZLStoNull Then
                     sSQL = "UPDATE " & sTable & " AS t " _
                        & " SET t." & sField & " = Null " _
                        & " WHERE t." & sField & " = """" " _
                        & ";"
                     Debug.Print sSQL
                     db.TableDefs.Refresh
                     DoEvents                     
                     db.Execute sSQL
                     nRecords = db.RecordsAffected
                     Debug.Print "----- " & Format(nRecords, "#,##0") & Space(9) & Now()
                     nCountNull = nCountNull + nRecords
                  End If
               End If 'data type is text
            Next oFld
         End If 'test for  system table
      End With 'tdf
   Next tdf

   sgTimeElapse = (Timer() - sgTimer1)
  
   sMsg = nCountFields & " fields in " & nCountTables & " tables checked " _
      & vbCrLf & Space(3) & Format(nCountTrim, "#,##0") & " values trimmed" _
      & vbCrLf & Space(3) & Format(nCountNull, "#,##0") & " ZLS set to Null" _
      & vbCrLf & vbCrLf & "     Elapsed Time: " & Format(sgTimeElapse, "#,##0.##") & " seconds"

   Debug.Print "   " & sMsg
   MsgBox sMsg, , "Done"

Proc_exit:
   On Error Resume Next
   Set oFld = Nothing
   Set tdf = Nothing
   Set db = Nothing
   SysCmd acSysCmdClearStatus
   Exit Sub

Proc_err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   LoopTables_TrimText"

   Resume Proc_exit
   Resume
  
End Sub
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

VBA > SQL > Loop and Trim Text

Screen shot

Example

Call LoopTables_TrimText()

Logic

Parameters

Optional:

Code

' LoopTables_TrimText

Share