Trim not working

S

Schwimms

I have a data set for names that comes over as such:

RYAN W FREITAS
DAN G GODBY

I tried using the trim function but it is not working...Any suggestions.
 
K

KARL DEWEY

Backup the database.
Use this function in an update query --
Replace([YourField], " ", " ")
This replace two spaces with one. You will need to run the query more than
once to complete the process.
 
K

Klatuu

Trim only handles leading and trailing spaces. Here is an expression that
will fix it:
Ordered by: Replace([Ordered by name]," ","")

Note there are two spaces in the second argument and no spaces in the third
argument. What is does is replace two adjacent spaces with an empty string,
so it will leave one space between each segment of the name.

--
Dave Hargis, Microsoft Access MVP


Schwimms said:
Ordered by: trim([Ordered by name])

BruceM said:
Post the code in which the function is used.
Click to expand...
Click to expand...
 
S

Schwimms

That works, I haven't used the update query's yet. Good stuff. Fits nicely
into the macro as well.

KARL DEWEY said:
Backup the database.
Use this function in an update query --
Replace([YourField], " ", " ")
This replace two spaces with one. You will need to run the query more than
once to complete the process.
--
KARL DEWEY
Build a little - Test a little


Schwimms said:
I have a data set for names that comes over as such:

RYAN W FREITAS
DAN G GODBY

I tried using the trim function but it is not working...Any suggestions.
Click to expand...
Click to expand...
 
K

Klatuu

Oops. It doesn't really work 100% of the time. I'll get back to you on
this, sorry.
--
Dave Hargis, Microsoft Access MVP


Schwimms said:
Ordered by: trim([Ordered by name])

BruceM said:
Post the code in which the function is used.
Click to expand...
Click to expand...
 
B

BruceM

I forget that Trim removes only leading or trailing spaces. Karl offered
one suggestion. Another is something like this, which I think will do it
all in one shot, assuming the format is always the same as you have shown:
Left([Ordered by name],Instr([Ordered by name]," ")) & Trim(Right([Ordered
by name],Len([Ordered by name]) - Instr([Ordered by name]," ")))

It would be best to separate the FirstName, MI, and LastName into separate
fields. If you are working with imported data that you are not storing, it
would probably be best to use a query to parse the data into the three
fields.

Schwimms said:
Ordered by: trim([Ordered by name] )

BruceM said:
Post the code in which the function is used.
Click to expand...
Click to expand...
 
Top