Seperate text in a column

H

hungfootball007

Hi All,

I have been trying to seperate the text in a column in Access but with
no sucess. Here is what i tried to do:

Ex:

Column field1
Lee
Sam
John Doe
Madona
Ho Lee

I would like to remove the space between these words like:

Column field1
Lee
Sam
John
Madona
Ho

Can someone help me please? Thanks much.
 
J

Jen

Are you removing the space, or dropping everything after the space? If
dropping everything, as your example shows, you can use a combination of
InStr to find the space, and the Left to pick off the part before the space.

j.
 
S

Steve Schapel

Hung,

You can use an Update Query, using your example, to update Field1 to...
IIf(InStr([Field1]," "),Left([Field1],InStr([Field1]," ")-1),[Field1])
 
S

Steve Schapel

.... I should have mentioned - make sure you have a backup of your
database before doing stuff like this :) !
 
Top