Delete part of each record

[Dimitris]

Hello,

In a field of a table there are entries with address and city in the same
record. For example: High street 55 Paris. I only want the address in that
field, so I need the city in the end of each record to be cut off. I guess
what I need is any word after the last number of the entry to be deleted.
Not just any number of each record, but after the LAST number of the entry
since there are streets that contain numbers as part of the street name for
example October 25th street.

Can someone please help.
Thank you
Dimitris

 

[Stefan Hoffmann]

hi Dimitris,

I only want the address
in that field, so I need the city in the end of each record to be cut
off. I guess what I need is any word after the last number of the entry
to be deleted. Not just any number of each record, but after the LAST
number of the entry since there are streets that contain numbers as part
of the street name for example October 25th street.

It's basically a real hard task...

Also a problem is the fact that the city may consist of more than one
word. E.g. La Paz.

So the only nearly working way here is quite simple:

Create a City table (ID, CityName).

Repeat this steps as necessary:

1) Add a new CityName, e.g. Paris.
2) Run a update query against your table, e.g.
UPDATE addressTable
SET City = 'Paris',
yourField = Trim(Left(Trim(yourField),
Len(Trim(yourField)) - Len('Paris')))
WHERE IsNull(City)
AND Len(Trim(yourField)) >= Len('Paris')
AND Right(Trim(yourField), Len('Paris')) = 'Paris';
3) Check the results.



mfG
--> stefan <--

 

[Dimitris]

Thanks for your answer Stefan,

There are a lot of records and there is a large number of cities which make
it difficult to enter them.
Isn't there a way to cut all the words after the last number of each entry?
Because that would be a solution. But I don't know how to do that.

Thanks again
Dimitris

 

[John Spencer]

It could be done with some VBA code.

Is there ALWAYS a number immediately before the city?
Is there ALWAYS a city name?

Here is an UNTESTED VBA function that might work for you. Copy it and paste it
into a module - be sure the module name is not the same as the function name.

Public Function fGetCityName(strIn)
Dim I as LONG, iPos as Long

If Len(StrIN & "") = 0 Then
fGetCityName = Null
Else
For I = Len(strIn) to 1 Step -1
If IsNumeric(Mid(StrIn,i,1))
iPos = i
Exit For
End IF
Next I

If iPos = 0 then
fGetCityName = Null
Else
fGetCityName = Trim(Mid(StrIn,IPos+1))
End if

End Function

Your update query might look like
UPDATE [SomeTable]
SET [CityField] = fGetCityName([AddressField]
WHERE [AddressField] Like "*[0-9]*"

AFTER you have populated the city field, you can use a second query to strip
off the city name. Perhaps something like the following.
UPDATE [SomeTable]
SET [AddressField] = Left([AddressField],Len([AddressField])-Len([CityField]))
WHERE [AddressField] LIKE "*" & [CityField]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County