Query Address Help

[allan320]

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

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, = Correct
Address2 = kilmarnock, Ayrshire ,= Should be kilmarnock
Address3 = nock, Ayrshire = Should be Ayrshire

tried all different ways and it meeses up can someone point me in the right direction.

Thanks

allan

 

[Malcy]

I think you problem lies in the address having two commas.
Perhaps you could first of all identify the position of the two commas in the string - I think you would use Len
You could have two variables lngComma1 and lngComma2. In your Kilmarnock example their values would be 18 and 30 respectively (if I counted correctly)
You could then use your InStr to catch between the positions of lngComma1 and lngComma2 for the town and to the right of lngComma2 for the county.
It certainly took me ages to work out the code when I had to do something similar but the infuriating thing is once you crack it then it looks so damned obvious!
Makes you weep!
Good luck

 

[RV]

allan,

you need a bit of VBA to sort this out, using the Split function.
These threads should get you started:

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=84&Number=1105240

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=82&Number=1091393

RV

 

[neileg]

The reason your formulas don't work is twofold. Mid() needs two parameters, a start and a finish. You are only supplying a start. In Address3, Instr() is finding the position of the first comma and Right() is counting back from the right hand end of the string.

As RV says, Split() should give you what you need.