I need urgent help!

[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

 

[GR]

have u tried to get the info by code and not using a query?
For example using the Split function. save the splitted
strings in different variables ex. City, No, street, etc.

 

[Jimmy]

Thanks for your message, but I am new in Access and I don't know how to do
what you suggested.
I need step by step help.

 

[Guest]

ok, since I don't know what exactly you want the info
splitted for...

1. Search in Access help for the Split Function
2. The expression argument is the full address
3. Specify de delimiter " "
4. you will get an array with the substrings

In your form supposing you have txtCity, txtStreet, txtNo,
etc

Me!txtCity = Array(x)
where x is the substring for the city.

It's been long since i used this function... have a look
in access help for examples.

 

[Dan Artuso]

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))

 

[tom]

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.