Remove trailing spaces from all fields with code

[niwot]

Have to import fixed width fields from old FoxPro. All field data must be trimmed. Lots of tables and many fields. Tried trim() in update query but it quit when it found a few records with actual spaces and nothing else. I guess you can't trim if there is nothing to trim against. Anyway can find these hard space fields with search/replace but also find fields with spaces between words that need to be kept. Example (x's are trailing spaces) Palm Beachxxxxx needs to be Palm Beach. Number of trailing space and hard spaces varies at the end of each field. Too time consuming to run the trim() funtion on every text field in every table with update query. Can I do this with code globally or by table? Only have to migrate this data once or twice but need to do it as quickly as possible. Can't change the field in FoxPro before importing. I am pretty good with VBA but no guru so make your answer as clear (easy, simple, idiot proof) as possible. Thank you.

 

[psemianonymous]

Access by default sets for text fields the "Allow zero-length strings" to False. Set this to True and your Trim() update should work. Or you can work around this by setting the fields to null, i.e.

iif( trim([fldname])="",
     trim([fldname]),
     Null)

 

[niwot]

I see how this will work field by field in a query but is there any way to do it code, iterating through a list of tables/fields? Thanks for the iif code if I have to do it in queries this will keep the query from crashing. Thanks again.

 

[psemianonymous]

There is a way, but it is long. Use the CurrentDB.Tabledefs collection to loop through each table, and each table's tbl.Fields collection to loop through fields.


Otherwise, you can do it manually. It is probably quicker to do it manually, unless you can find another thread that does basically the same thing.

 

[fullkeel]

Perhaps I am missing something here but rather than use Trim(), why wouldnt you use RTrim() instead?