Trimming leading spaces from fields

[Guest]

I have a requirement for my database to trim all data within all columns of
all leading and trailing spaces. My issue is that my code takes a long time
to process. I have 45 columns and there is alot of data within the table
itself.

The way I have been tackling this is through individual update SQL
statements as per:

DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Member_Number] =
(Trim([Member_Number]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Title] = (Trim([Title]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [First_Name] =
(Trim([First_Name]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Middle_Name] =
(Trim([Middle_Name]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Surname] =
(Trim([Surname]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [DOB] = (Trim([DOB]));"

etc etc etc

This is extremely slow.

Does anyone have a better way of doing this?

Cheers

Neil

 

[pietlinden]

Neil,
why not just run all the updates at the same time?

UPDATE MyTable
SET MyField1=Trim$(MyField1),
MyField2=Trim$(MyField2),
.....
WHERE....

 

[John W. Vinson]

I have a requirement for my database to trim all data within all columns of
all leading and trailing spaces. My issue is that my code takes a long time
to process. I have 45 columns and there is alot of data within the table
itself.

The way I have been tackling this is through individual update SQL
statements as per:

DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Member_Number] =
(Trim([Member_Number]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Title] = (Trim([Title]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [First_Name] =
(Trim([First_Name]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Middle_Name] =
(Trim([Middle_Name]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Surname] =
(Trim([Surname]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [DOB] = (Trim([DOB]));"

etc etc etc

This is extremely slow.

Does anyone have a better way of doing this?

Do all the fields in all the records have spaces? If so... WHY? Access won't
insert them if you're entering data from a form.

If only a portion of the records/fields need to be edited, you can by all
means use a criterion to filter the records:

DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Title] = (Trim([Title])) WHERE
[Title] LIKE " *";

If the field is indexed this will update only those records which need it.

Of course, ONLY text fields *can* have leading or trailing blanks. Numbers,
currency, date/time and yes/no fields simply don't let you store blanks in the
first place, so you needn't run queries on them at all.

Your best bet is to prevent the blanks from being entered in the first place
though!

John W. Vinson [MVP]

 

[John Spencer]

When importing data it is very possible to have leading and trailing spaces
in the field. I would make one slight change to John Vinson's code since
his SQL only checked for leading spaces.

UPDATE SYS_TBL_FILE_ENTRY
SET [Member_Number] = Trim([Member_Number])
WHERE [Member_Number] Like " *" or [Member_Number] Like "* "

And as noted elsewhere, you can do all the fields in one pass. Although
this may negate the benefit of using the where clause to a large extent
UPDATE SYS_TBL_FILE_ENTRY
SET [Member_Number] = Trim([Member_Number])
, [First_Name] = Trim([First_Name])
WHERE [Member_Number] Like " *" or [Member_Number] Like "* "
OR [First_Name] Like " *" or [First_Name] Like "* "


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a requirement for my database to trim all data within all columns
of
all leading and trailing spaces. My issue is that my code takes a long
time
to process. I have 45 columns and there is alot of data within the table
itself.

The way I have been tackling this is through individual update SQL
statements as per:

DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Member_Number] =
(Trim([Member_Number]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Title] =
(Trim([Title]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [First_Name] =
(Trim([First_Name]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Middle_Name] =
(Trim([Middle_Name]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Surname] =
(Trim([Surname]));"
DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [DOB] = (Trim([DOB]));"

etc etc etc

This is extremely slow.

Does anyone have a better way of doing this?

Do all the fields in all the records have spaces? If so... WHY? Access
won't
insert them if you're entering data from a form.

If only a portion of the records/fields need to be edited, you can by all
means use a criterion to filter the records:

DoCmd.RunSQL "UPDATE SYS_TBL_FILE_ENTRY SET [Title] = (Trim([Title]))
WHERE
[Title] LIKE " *";

If the field is indexed this will update only those records which need it.

Of course, ONLY text fields *can* have leading or trailing blanks.
Numbers,
currency, date/time and yes/no fields simply don't let you store blanks in
the
first place, so you needn't run queries on them at all.

Your best bet is to prevent the blanks from being entered in the first
place
though!

John W. Vinson [MVP]

 

[John W. Vinson]

When importing data it is very possible to have leading and trailing spaces
in the field. I would make one slight change to John Vinson's code since
his SQL only checked for leading spaces.

ooopS!!! Thanks John, I did space on that.

UPDATE SYS_TBL_FILE_ENTRY
SET [Member_Number] = Trim([Member_Number])
WHERE [Member_Number] Like " *" or [Member_Number] Like "* "

And as noted elsewhere, you can do all the fields in one pass. Although
this may negate the benefit of using the where clause to a large extent
UPDATE SYS_TBL_FILE_ENTRY
SET [Member_Number] = Trim([Member_Number])
, [First_Name] = Trim([First_Name])
WHERE [Member_Number] Like " *" or [Member_Number] Like "* "
OR [First_Name] Like " *" or [First_Name] Like "* "

I would again emphasize that this need be done *only* for Text fields. The
query will run (and eat time) on other datatypes, but it will never find any
blanks to trim; instead it will convert the date or other data to a String
(with no blanks!!), trim it, and then convert it back - an absolute waste of
time.

John W. Vinson [MVP]