Help with code needed

[Keith]

I have imported address data into my DB however the Post Code is on the end of the second line of the adrresss and I need to strip it out and put it in a seperate field.
The problem I have is that the post code length varies between 7 characters, including a space, and 8 characters. eg.

Hardingstone Northamptonshire NN4 6EL
Wellingborough Northamptonshire NN14 1AY

I am new to VBA and would much appreciate a nudge in the right direction.

TIA
Keith

 

[jwindon]

There is sure to be some kind of "looping until a textspace" code out there, but I wonder if using the Right function in a query wouldn't work. As I can see, if you set the Right to 8...you will get all the postal zip. The zips with 7 would come in with a space on them. If Access doesn't drop them, then do an update query that runs a Left on 7 those that Left 1 = " " to take out the leading space.

Clear as mud, but it was the first thing that came to my mind?!?!!

[This message has been edited by jwindon (edited 11-29-2001).]

 

[pcs]

sometimes mud is pretty clear!

keith, assuming your data always is as you described.

jwindon has the easy and elegant solution...

you could do it in one step with: Trim(Right([YourAddressField],8))

al

 

[Keith]

Thanks guys i'll try your suggestions when I get to work today.

Keith