Splitting uneven text field.

[swaroop1012]

HI all,

I have two problems to discuss in this thread. I am working on Access 2002.

the queries are:

1. I had a address field with carriage returns in the middle. I used some code to remove the carriage returns and it worked fine. But the problem is som data has "," character at the end of the address text. Is there any way to remove the unwanted character at the end of the text.

2. Also i want to seperate the street data from the address field.

Eg:
Stanion Road,Brigstock,Kettering,Northamptonshire,NN14 3HW,

Bowling Green Recreation Ground,High Street,Islip,Northamptonshire,NN14 3JS

1 Riverside Way,Islip,Kettering,Northamptonshire,NN14 3LF,

In the above examples you can see the "," character at the end. I want to delete that.

Also i want to seperate the terms like "stanion Road", "High street", "Riverside Way" etc. from the address field.

Can anyone please help me in resolving these issues. Thanks in advance.

Swaroop

 

[HiTechCoach]

HI all,
1. I had a address field with carriage returns in the middle. I used some code to remove the carriage returns and it worked fine. But the problem is som data has "," character at the end of the address text. Is there any way to remove the unwanted character at the end of the text.

Ti remove the ending , try this:

IIF(Right([YourFieldName],1) = "," , Left([YourFieldName],Len([YourFieldName])-1, [YourFieldName])

 

[DCrake]

If you want to seperate the different elements of the address I suggest you use the Split() function to extract the different address lines.

Split(Address,",")

For more information search access help on Split()

David

 

[swaroop1012]

Ti remove the ending , try this:

IIF(Right([YourFieldName],1) = "," , Left([YourFieldName],Len([YourFieldName])-1, [YourFieldName])

To be a bit clear, here is what i did.

I have a table ABC which has the field "Address". This address has carriage returns and i used the following code to remove the carriage returns in an append query.

Expr3: Replace([Address],Chr(13),",")

and in the append to row of the query, gave the field name "Addressupdate".

So my problem now is where exactly i need to include the code you mentioned.

Thanks in advance.

 

[SimonB1978]

Hi,

You could do an Update query to correct what is already in your table:

UPDATE table_name SET YourFieldName = IIF(Right([YourFieldName],1) = "," , Left([YourFieldName],Len([YourFieldName])-1, [YourFieldName])

That will "clean up" your trailing "," in your table.

Simon B.

 

[Atomic Shrimp]

Are the fields consistently-structured? (they don't look as if they are, if your example data is representative of the real thing).

If the street name isn't always the same position in the comma separated values, it will be difficult to split this into fields for street name, town, postcode, etc - the best you're going to get is AddressLine1, AddressLine2, AddressLine3, etc - still useful for printing addresses, but maybe not so good if you want to sort or group by town.

 

[SimonB1978]

Hi

As for the splitting, if your format is always ends with ..., Address, City, Province(?), Postal Code(?), then you can use Split() as DCrake suggested and go backwards for filling the fields (postal code, province, city, etc.)

Simon B.

 

[gemma-the-husky]

tidying addresses -

this is very tricky, because of the variations - you can take out punctuation, but even here you may remove something you want.

as has been pointed out, the problem is, is that if the streetname/town arent always in the same position, then you struggle to do this automatically

this is why, websites eg, split addresses into separate fields, and make certain ones mandatory - and they will still have issues, because there is a difference bewtween

village, county

and

suburb, city, county

----------
depends how many you have, but if its critical you may need to tidy your data manually to start - and then change the input routine to accept addresses differently.

extracting towns would be a bit easier, but street names harder.

----------
if you have a lot of addresses, and are happy to pay for this, you might need the Post Office Post Code data (postcodes are copyrighted), which will give you address structure based on post codes - but its very structured, and not so easy to use.

a different - you could try sending the addresses to google maps, i think that would return it structured properly - this is free, but google maps will ignore UK postcodes (but will work correctly with full addresses)

 

[swaroop1012]

Hi,

You could do an Update query to correct what is already in your table:

UPDATE table_name SET YourFieldName = IIF(Right([YourFieldName],1) = "," , Left([YourFieldName],Len([YourFieldName])-1, [YourFieldName])

That will "clean up" your trailing "," in your table.

Simon B.

Hi Simon,

When i tried this command, it is giving syntax error. the error is "wrong number of arguments. any help pls.

 

[SimonB1978]

Hi,

Missing a )

UPDATE table_name SET YourFieldName = IIF(Right([YourFieldName],1) = "," , Left([YourFieldName],Len([YourFieldName])-1), [YourFieldName])

That should work

Simon B.

 

[namliam]

Stanion Road,Brigstock,Kettering,Northamptonshire,NN14 3HW,
Bowling Green Recreation Ground,High Street,Islip,Northamptonshire,NN14 3JS
1 Riverside Way,Islip,Kettering,Northamptonshire,NN14 3LF,

This looks like this should actually be compared to
,Stanion Road,Brigstock,Kettering,Northamptonshire,NN14 3HW
Bowling Green Recreation Ground,High Street,Islip,Northamptonshire,NN14 3JS
,1 Riverside Way,Islip,Kettering,Northamptonshire,NN14 3LF

To get the street in the second column always which you want out right??
If so removing the trailing comma would be BAD!