I need urgent help!

  • Thread starter Thread starter Jimmy
  • Start date Start date
J

Jimmy

Hello I am in a desperate situation.
I have a table in which a field called ADDRESS contains data such as
"postcode city country address streetnumber" for example:
104 53 Athens Greece - Socratous 55. (Socratous is the street name) I need
to split that data in two new fields one called CITY which should contain
postcode city and country, and another field called STREET which should
contain streetname and streetnumber.
What I actually need is to have the data on the left of the symbol " - " in
one column and the data on the right of the symbol " - " in another column.
I just don't know how to make that querie. I hope someone can help me
because I have a big pressure of time and I am desperate.
Thank You Jimmy
 
Hi,
Try this (untested)

Update yourTable Set City = Trim(Left([Address],InStr(1,[Address],"-",1) -1)),
Street = Trim(Mid([Address],InStr(1,[Address],"-",1) + 1))
 
Dan's solution seems good... but if you have problems with Access try
copying all your data into MS Word, which will show it in a Table... select
the whole table and convert it to Text... Use the Replace facility to
replace all dashes with a Tab character... select all the text again
(without the final paragraph mark) and insert a table, and you will have all
the data split across two tables... select the table and paste it back into
Access in two columns... But I would suggest work on using an Update query
as suggested by Dan!

Tom.
 
Another quick tip, copy and paste the data into excel, do
a data/text to columns on the character "-" and then bring
the two columns back into your database. Sometimes this is
faster if you aren't adept at queries.
-----Original Message-----
Hi,
Try this (untested)

Update yourTable Set City = Trim(Left([Address],InStr(1, [Address],"-",1) -1)),
Street = Trim(Mid([Address],InStr(1,[Address],"-",1) + 1))

--
HTH
Dan Artuso, Access MVP


Hello I am in a desperate situation.
I have a table in which a field called ADDRESS contains data such as
"postcode city country address streetnumber" for example:
104 53 Athens Greece - Socratous 55. (Socratous is the street name) I need
to split that data in two new fields one called CITY which should contain
postcode city and country, and another field called STREET which should
contain streetname and streetnumber.
What I actually need is to have the data on the left of the symbol " - " in
one column and the data on the right of the symbol " - " in another column.
I just don't know how to make that querie. I hope someone can help me
because I have a big pressure of time and I am desperate.
Thank You Jimmy
Click to expand...


.
Click to expand...
 
Back
Top