Trim Access Fields

[JasonS]

Does anyone know how to trim blank spaces from Access text fields?

I want to loop through all of the tables and all fields in the table and check to see if it is type string. If it is, then I want to trim the field's value to get rid of spaces.

Thanks.

[kovan]

sorry i dont know if this will answer your question
but how i undrestand is
you want to trim spaces OF THE SIDES of a string
not within a string?
like " hello world"
would trim one space of the left, not one from left and middle?

if thats the a case

open your table up (using sql or the ado object)

make a do, or a while loop
and using the .EDIT property of ado object
trim the fields
so like this

With MyObject
do until .eof
.edit
!myfield1 = Trim(!myfield)
!myfield2 = Trim(!myfield2)
.moveNext
loop
end with

i hope that helps

[Tonatiuh]

Good answer Kovan, but if the field has a null value you'll get an error. You can use the IsNull() function first to know if the field has a null value or easier you can use the TRIM function as it follows:

!MyField = TRIM("" & !MyField)

Good look!

[Paul Warren]

Instead of manually coding each field try this :


Private rsTemp As New ADODB.Recordset
Private cnTemp As New ADODB.Connection

Private Sub Form_Load()

Dim vField As Field

cnTemp.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\db1.mdb"
cnTemp.Open

rsTemp.Open "SELECT * FROM Table1", cnTemp, adOpenStatic, adLockOptimistic, adCmdText

' Assume that the provider supports recordcount
If rsTemp.RecordCount = 0 Then
Exit Sub
End If

Do Until rsTemp.EOF

' Loop through the fields checking the type
For Each vField In rsTemp.Fields
If vField.Type = adVarChar Then
' By nesting the IF's the second one is only run against
' text fields, speeding up the loop
If Not IsNull(vField.Value) Then
'Edit the field in here
rsTemp.Fields(vField.Name) = Trim(vField.Value)
End If
End If
rsTemp.Update
Next
rstemp.MoveNext
Loop

End Sub