Getting rid of white space

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello to all,

I imported an excel database into Access 2003 but when I open my mailing
list all the records have extra white space at the end. Even though the tags
in the mailing list are surrounded by the " =TRIM " function it does not
purge the trailing space from the end of the record.

Is there a way to automate the removal of the trailing space, or must I
remove it manually from each record? The database has several thousand
records in it, so it will take forever trying to fix things manually.

I'm not sure how to proceed with this. Please help!

Sky Warren
 
Hi Sky.
With the "Blank Spaces at the end" I think you are refering to blank records
at the end of the imported table. The Trim() function will not address that
type of records. I have approached that with modifying the design of the
data table, where I require an entry in the specific fields, if no entry
exists this record would be diverted to a Paste Errors table.
Hope this helps.
Fons
 
Hello Fons,

Actually, I don't think they're blank records for this reason. When I open
my form which is based off the same table as the mailing list and I put the
curson in say the name field, there is padded space at the end.

This is what I've done:

I open my form called clients and put the cursor in a random field, lets say
the name field. I then hit the End key and the cursor moves to the end of the
field revealing trailing space. I then use the Backspace key to remove the
trailing space and save the change. Once I do that, I reopen the mailing list
and the trailing space is gone. I've removed the trailing space from several
fields in the database with this method, but it's very time consuming.

I just can't figure out why the Trim() function is not removing the trailing
space. Currently my mailing list is displaying the trailing space with each
record that contains whitespace so it's very ragged looking and splits across
rows, which isn't going to work for printing labels.

If anyone can figure this out I would be greatly appreciative.
 
Trim should work for you if the white spaces are strictly spaces and don't have
some other character at the end. Are you trimming each field or are there
multiple fields concatenated together and then trimming that?

I would try running an update query on the table and trimming each text field.

UPDATE YourTable
Set FieldA = Trim(FieldA),
FieldB = Trim(FieldB),
FieldC = Trim(FieldC)

I would avoid trying to non-text fields.
 
Hi John,

I tried the Update Query and got strange results. Following is an example of
steps I did:

After creating the Update Query in the section called "Update To:" I tried
the following TRIM statement variations in the Address field:

=TRIM("Clients.[Address]")
=TRIM("[Address]")
=TRIM("Address")

After running the query every record in the Address field was changed to
either Clients.[Address], [Address] or Address. I don't know why the data is
being changed instead of trimmed. Am I doing something wrong John?
 
I tried the Update Query and got strange results. Following is an example
of
steps I did:

After creating the Update Query in the section called "Update To:" I tried
the following TRIM statement variations in the Address field:

=TRIM("Clients.[Address]")
=TRIM("[Address]")
=TRIM("Address")

After running the query every record in the Address field was changed to
either Clients.[Address], [Address] or Address. I don't know why the data
is
being changed instead of trimmed. Am I doing something wrong John?
Click to expand...

You should have left off the quotes.

=TRIM([Address])

In BASIC, the quotes mean to take what's inside them literally.

Tom Lake
 
To Tom, John and Phons:

Thank all of you guys for your help. I successfully trimmed the white space
from my table and the label report looks fabulous now. I wish all of you much
success in your futures, and I am so very grateful that you were willing to
share your knowledge for everyones benefit. God bless!

YOU GUYS ROCK!!!


Tom Lake said:
I tried the Update Query and got strange results. Following is an example
of
steps I did:

After creating the Update Query in the section called "Update To:" I tried
the following TRIM statement variations in the Address field:

=TRIM("Clients.[Address]")
=TRIM("[Address]")
=TRIM("Address")

After running the query every record in the Address field was changed to
either Clients.[Address], [Address] or Address. I don't know why the data
is
being changed instead of trimmed. Am I doing something wrong John?
Click to expand...

You should have left off the quotes.

=TRIM([Address])

In BASIC, the quotes mean to take what's inside them literally.

Tom Lake
Click to expand...
 
Back
Top