Access VBA manipulate UK Postcodes

[VBA Beginner]

VBA help needed to manipulate UK Postcodes

Hi all,

I'm trying to mainpulate UK postcodes in MS Access (2003 or 2007). What I need is to split UK postcode in to 4 parts, therefore:

SO10 1QW would be: SO 10 1 QW
M6 5RT would be: M 6 5 RT
WC1X 0BH would be: WC 1X 0 BH
M27 8FZ would be: M 27 8 FZ
BR2 8HX would be: BR 2 8 HX

I've managed to get the 1st, 3rd and 4th parts split out by removing either numbers or letters, however the 3rd part is where I'm stuck as it can be both alpha/numerical.

My only thought is to find the 1st numercial character and return all characters from there until the <space>.

Any ideas on how to do this (or any other solution)?

Thanks!!

Glen

 

[VBA Beginner]

Just in case anyone ever needs to do the above, here we go......

1st split the postcode in to 2 parts (using the space as a seperator) so you should have a left split (e.g. SO10) and a right split (1QW). Then use the following code:

IIf(Len([Left Split])=4,Right([Left Split],2),Bart_Simpson([Left Split]))

Bart_Simpson uses the below VBA code:

Public Function Bart_Simpson(InString As String) As String
'-- Returns only printable characters from InString - numercial values
Dim x As Integer
For x = 1 To Len(InString)
If Asc(Mid(InString, x, 1)) >= 48 And Asc(Mid(InString, x, 1)) <= 57 Then
Bart_Simpson = Bart_Simpson & Mid(InString, x, 1)
End If
Next x

End Function

Glen

 

[VBA Beginner]

Apologies, the above does not work for all UK postcodes (you can have W1X 8HX), back to the drawing board!!

 

[chergh]

Whats the objective here? Are you trying to validate that postcodes are valid or something?

 

[VBA Beginner]

The objective is to split the postcodes in to those 4 segments to allow them to be uploaded in to a black box (cannot view internal structure) IT system.

Therefore the fundemental task is to split them up!

 

[chergh]

Wow sounds like an ill thought out requirement.

Best bet would be to use the "split" function with the space as a delimiter and then use the code you have to divide the two halfs you will get returned from the split function.

 

[VBA Beginner]

I have split out the 2 halfs with the space as the delimiter, however the code is not suitable to then split the 1st half again. I thought it was however it did not extract the correct data for postcode in the format of W1D 8HX.

Very annoying but on the bright side as it's very quiet on here in relation to the task it's obviously a very difficult task (so I don't feel to bad!!).

Thanks for your thoughts, one to pounder over the bank holiday!!!

 

[chergh]

This should work for the first half.

Sub blah()

Dim i As Long
Dim firstquarter As String
Dim secondquarter As String

strString = "M27"

i = 1

Do While IsNumeric(Mid(strString, i, 1)) = False

    firstquarter = quarter & Mid(strString, i, 1)
    
    i = i + 1
    
Loop
    
secondquarter = Mid(strString, i, Len(strString) - i + 1)

Debug.Print firstquarter
Debug.Print secondquarter

End Sub

 

[Brianwarnock]

Is

W1D 8HX.

a valid post code.
I thought postcodes were
Alpha 1-2 characyers
Numeric 1-2 characters
space
numeric 1 character
alpha 2 characters.

Brian

 

[VBA Beginner]

Thanks Chergh I shall give this a go (although I do not think it will work for all types of postcode format). Pain is there are 1.8 million of them so it takes ages to run on my machine!!!

Brian - Unfortunately it is a valid postcode, these are based in London, you also get WC1X 0BH. This is why it's such a nightmare to do this, added to that it needs to be done on a monthly basis by someone pressing a button so it must be fully automated (ideally I'd split the file in 2, pull it in to Excel and do it that way!!.

 

[Brianwarnock]

As it is nonstandard is there a list of them that could be dealt with separately?

brian

 

[VBA Beginner]

Possibly (I'll Google it), although I would still need to know how to deal with them separately?

 

[chergh]

What you're doing really isnt that hard.

Split function to split the postcode into 2 halves

For the first half parse the string until you come to a numeric value thats the first part you need. The remainder of the string is the second part you need.

For the second half parse the string until you come to a non numeric value which is the third part of the string you need, the remainder of the string is the final part you need.

Done.

 

[Brianwarnock]

Having looked at chergh's code I don't think it matters, it takes care of it. Test on a set of sample codes.

Brian

Edit I see Chergh came back and explained it.

 

[VBA Beginner]

Hi all,

Thanks for all your help, now solved with the following code

Public Function Homer_Simpson(InString As String) As String
'***When a numeric is found returns all characters from that numeric***
Dim x As Integer
For x = 1 To Len(InString)
If IsNumeric(Mid(InString, x, 1)) Then
Homer_Simpson = Homer_Simpson & Mid(InString, x)
Exit For
End If
Next x

End Function

Think I may have another question on re-setting autonumber, however still searching the forum!!

Thanks again

Glen