Parsing

[goslincm]

I have not used the replace function in Access and need to take a field which is in this format: .Beaver Township and create a new field with only the word: Beaver

Can anyone help me with this?

 

[VRoscioli]

Will the following do it?

Replace(Replace([FieldName],".","")," Township","")

-V

 

[goslincm]

How or where do I put this into my query? I'm in design view, do I place it in the fieldname that I need to replace, or do I do an put in a new field name and put this information after that new field name?

 

[goslincm]

Alright, sorry so I got you idea and it works, however, I noticed that my fieldname has some townships and some cities and some counties. All I want is the name and not the type. How can I remove whatever the type is after the name?

 

[AlexCuse]

How about this?

select left(replace([FieldName], '.'), InStr([FieldName], ' ') -2)
from SomeTable

Normally you'd just need to use -1, but I think it's better to do -2 than to replace the period twice. If you could have a varying number of periods, you may consider replacing at both points.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[VRoscioli]

One solution would be to add more layers of Replace() functions:

Replace(Replace(Replace(Replace([FieldName],".","")," Township","")," City","")," County","")

If there are many different qualifiers (e.g. "Township") this may become unwieldy. If you do not like this solution, you need to more clearly define how you know when the name ends. If it is always one word (e.g. "Beaver" and not "West Beaver", you could try searching for a space:

Mid([FieldName],2,InStr([FieldName]," "))

-V

 

[AlexCuse]

To get a few more shots in on this horse, you could also assume that the word being removed at the end will only be one word, and search for a space that way:

Left(Replace([Column],'.',''),InStrRev([Column],' '))

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom