TRIM text in Access table!!! Help!!!

[Artesha]

I imported an MS Excel file into Access and some of the records have a space in the front. There are about 1000 records and I don't want to go and manually correct. Is there a way for me to correct this for all of the fields at one time?

Thanks.

Artesha

 

[DanJR]

Yes,

-an update query with the trim function:

[tt]

UPDATE myTable SET myTable.myField = Trim([myField])

[/tt]

Hope this helps,
Cheers,
Dan

 

[Artesha]

Dan,

Thanks a lot! It worked like a charm )

Artesha

 

[DanJR]

Hi Artesha,

I just realised i didn't read your just post correctly. Here is a way to update ALL fields at once:
[tt]
Public Function TrimAllFields(TableName As String) As Boolean
'field all rows of all fields in a table

Dim fld As DAO.Field
Dim strSQL As String
Dim db As Database

on error goto errUpdate

'show busy
DoCmd.Hourglass True

Set db = CurrentDb

'loop through fields
For Each fld In db.TableDefs(TableName).Fields
'create an update SQL statement for each field
strSQL = "UPDATE [" & TableName & "] SET [" & TableName & "].[" & fld.Name & "] = Trim([" & fld.Name & "])"
'run the query
CurrentDb.Execute strSQL, dbFailOnError 'roll back if error occurs
Next fld

TrimAllFields = True 'it worked

CleanUp:
DoCmd.Hourglass False
Set fld = Nothing
Exit Function

errUpdate:
MsgBox "Error occured triming fields in " & TableName, vbExclamation, "Error"
Resume CleanUp

End Function
[/tt]

Just paste the above function into a module. Goto the debug menu or immediate window (Ctl+G) and type TrimAllFields("myTable&quot.

Cheers,
Dan

 

[judgehopkins]

Dan, I tried this function but Access complains that it is expecting a procedure not a function.

Also, is there a way to include vbProperCase-ing all the entries, traps for null or empty fields, and removing triple or double spaces?

I am trying to update an imported database that had really bad data normalization; I am just trying to clean it up a bit.

Thanks!

"The potentialities of a language are so vast that we must wonder sometimes at ourselves: how do we manage to know so much?" Anthony Burgess, A Mouthful of Air