DELETE PART OF A FIELD IN ACCESS

[Guest]

I have a text field that lists "state" "county" "city" for all zip codes in
a given state. An example would be

oh/franklin/columbus

Is there a way to remove all data before the city? I want the above example
to read only: columbus

I would like to remove all data up to and including the second "/" keeping
in mind that the city and county field sizes vary.

Thanks in advance for any help!
Charlie

 

[Guest]

There are probably several ways to go about this. Are you wanting to do a
one time split so you can store them in seperate fields (as they should be)?
Assuming so you need to write a function to loop through the recordset
reading out the text field into an array with the built in SPLIT function
(look in Access Help for information on it) and then assign the values to the
correct fields. You could also split with a some what complex combination of
using the INSTR and MID functions.

-Steve Huff
http://www.huffs.us

 

[fredg]

I have a text field that lists "state" "county" "city" for all zip codes in
a given state. An example would be

oh/franklin/columbus

Is there a way to remove all data before the city? I want the above example
to read only: columbus

I would like to remove all data up to and including the second "/" keeping
in mind that the city and county field sizes vary.

Thanks in advance for any help!
Charlie

If your version of Access has the InStrRev() functions, you can use:

=Mid([FullField],InStrRev([Fullfield],"/")+1)

You can also use the Split() function.

 

[John Vinson]

I have a text field that lists "state" "county" "city" for all zip codes in
a given state. An example would be

oh/franklin/columbus

Is there a way to remove all data before the city? I want the above example
to read only: columbus

I would like to remove all data up to and including the second "/" keeping
in mind that the city and county field sizes vary.

Thanks in advance for any help!
Charlie

There's an obscure but handy feature of InStr that will let you do
this: an optional first argument specifies where in the string to
*start*.

Mid([yourfield], InStr(InStr([yourfield], "/") + 1, [Yourfield], "/")
+ 1)


John W. Vinson[MVP]