trim function

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

I have a table with 80 columns in it that I imprted from another program.
Aside from doing each individually, is there a way in an update query that I
can trim all fields at once??
 
UPDATE YourTable
Set YourFieldA = Trim([YourFieldA])
, YourFieldB = Trim([YourFieldB])
, YourFieldC = Trim([YourFieldC])
, YourFieldD = Trim([YourFieldD])
, YourFieldE = Trim([YourFieldE])
....

ONLY include your text and memo fields otherwise you are liable to get
an error
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
UPDATE YourTable
Set YourFieldA = Trim([YourFieldA])
, YourFieldB = Trim([YourFieldB])
, YourFieldC = Trim([YourFieldC])
, YourFieldD = Trim([YourFieldD])
, YourFieldE = Trim([YourFieldE])
...

ONLY include your text and memo fields otherwise you are liable to get
an error
'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================
I have a table with 80 columns in it that I imprted from another program.  
Aside from doing each individually, is there a way in an update query that I
can trim all fields at once??
Click to expand...
Click to expand...

I guess she could loop over the table fields and update just the text
fields... or is that gilding the lily?

Public Sub TrimAllTextColumns(ByVal strTable As String)
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String

Set tdf = DBEngine(0)(0).TableDefs(strTable)
For Each fld In tdf.Fields
If fld.Type = dbText Then
strSQL = "UPDATE [" & strTable & "] SET [" & fld.Name & "]
= Trim([" & fld.Name & "])"
DBEngine(0)(0).Execute strSQL, dbFailOnError
Debug.Print fld.Name, DBEngine(0)(0).RecordsAffected
End If
Next fld
End Sub
 
Back
Top