Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 27th, 2006, 03:49 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default Splitting an address field

I have an address all in the one line at the moment. My front end has been programmed this way. Now I have to split the address into 3 or 5 lines. The address looks like this:

3 Thorn Road Edinburgh Scotland G68 2AA

Some are spaced with a space and some have a return press between the lines.

Does anyone know how to do this so that it doesnt just take in 3 thorn etc

Thanks

Allan

 
Old June 27th, 2006, 06:58 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Read the help for the SPLIT, LEFT, RIGHT, and MID functions. You should be able to do something with those.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old June 29th, 2006, 03:54 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok this is still getting the better of me. Some of the addresses that the users have put on the system have been seperated using spaces, return keys and commas. Some have no house numbers etc. So im stuck on how to do this as the query i have is giving me the wrong data into the fields.

Allan

 
Old June 29th, 2006, 06:53 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Try working your way from back to front.

Start with the right end. You know that the postal code is of a certain length and format. Look for that. Then work your way backward through country, city, etc. If you can identify the city (at least), then the address won't matter because you will then just take everything left of the city to the beginning, whether there is a number or not.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old June 29th, 2006, 07:24 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The post code is in a seperate field so that makes it easier for me.

This is the query i have.

UPDATE Clients SET Clients.Address1 = Left([Address],InStr([Address],",")), Clients.Address2 = mid([Address],InStr([Address],",")), Clients.Address3 = Right([Address],InStr([Address],","));

If it was looking for this address: 34 Thornwood Road, Kilmarnock, Aryshire. It would produce

Address1 = 34 Thornwood Road,
Address2 = kilmarnock, Ayrshire ,
Address3 = nock, Ayrshire

tried all different ways and it meeses up

Thanks

allan




 
Old June 30th, 2006, 05:43 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, got a new method of doing it. but still coming up with the same results :(

Code:
SELECT [Clients].[Address], Left([Clients].[address],InStr([Clients].[address],", ")) AS myNumeric, Mid([Clients].[address],InStr([Clients].[address],",  ")+1,Len([Clients].[Address])-InStr([CLients].[address],"APT")+InStr([Clients].[Address],", ")-1) AS myStreet, Right([Clients].[Address],InStr([Clients].[address],", ")) AS myApt
FROM Clients;
Anyone have any suggestions

Allan

 
Old July 3rd, 2006, 07:46 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I would suggest taking all the data out into Excel and doing the ETL by hand.
Then rebuild that table structure to allow the proper input of the address fields.
Since each user has the opportunity to enter data however they want to, it is never going to work exactly right without tons of processing.


mmcdonal
 
Old July 4th, 2006, 05:31 AM
Authorized User
 
Join Date: May 2006
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, i know. We basically decided to start afresh. Add 5 fields into our form and get the users of the database to update the data as it is needed to be used. Only 1 record is used at a time, so it should be easy enough for them to update.

Thanks ;)

Allan

 
Old July 25th, 2006, 03:35 PM
pjm pjm is offline
Authorized User
 
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I don't have any code for you but I do have a few suggestions:

    1. Write a parsing function in VBA to be used in a query. It's easier to work out
the code when you don't have to put it in one long expression in your SQL statement.

    2. I find that writing a simple grammar to completely describe my data helps:

    <address> ::= <street number> <street name> <street type> <town> <country> <postal code>
    <street number> ::= NUMBER | ORDINAL
    <street name> ::= STRING
    <street type> ::= Street | Road | Drive | Avenue | ...
    <town> ::= STRING
    <country> ::= STRING
    <postal code> ::= <3 letters or digits> BLANK <3 letters or digits>


-Phil-




Similar Threads
Thread Thread Starter Forum Replies Last Post
Splitting a attribute value gantait XSLT 6 February 19th, 2007 11:57 AM
Splitting of string lily611 SQL Server 2000 3 March 19th, 2005 08:17 AM
Retreiving IP address & gateway address sjangit VBScript 0 February 3rd, 2004 02:02 PM
string splitting qwjunk Classic ASP Basics 5 February 2nd, 2004 05:59 AM
splitting this string menesesg Classic ASP Basics 1 September 10th, 2003 12:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.