UK postcode issue

I need to split the UK postcodes in my db down to 4 types. For example, the
original POSTCODE field of AB10 6YT needs to become :

AB
AB10
AB10 6

In the UK we use postcodes of slightly 'unequal' length, so we could have
AB10 6YT or AB1 6YT.

What query could I use to accomplish this?

Thanks,
Lap

I would do it in VBA by parsing the postcode into the two numeric and
two alpha components (remembering that some London (and possibly
other) codes have an extra single letter after the first number - e.g.
EC1S 4JJ) and then putting the requisite "bits" together to create
your larger and smaller areas. You could do it with lots of nested
IIF()s and Mid()s, but I wouldn't!

On Wed, 9 Jun 2004 14:57:49 +0100, "Lapchien"
wrote:

I need to split the UK postcodes in my db down to 4 types. For example, the
original POSTCODE field of AB10 6YT needs to become :

AB
AB10
AB10 6

In the UK we use postcodes of slightly 'unequal' length, so we could have
AB10 6YT or AB1 6YT.

What query could I use to accomplish this?

Thanks,
Lap



Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Well, this is what I am using -

Function ShortPO(strCode As String) As String
Dim strHold As String
Dim LongCnt As Long

LongCnt = 1

Do Until IsNumeric(Mid(strCode, LongCnt, 1))
strHold = strHold & Mid(strCode, LongCnt, 1)
LongCnt = LongCnt + 1
Loop
ShortPO = strHold

End Function

But When I run my query, although it returns the first part of the post code
I get out of memory errors...

Anyone have any thoughts?

Lap



"Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in message
news
I would do it in VBA by parsing the postcode into the two numeric and
two alpha components (remembering that some London (and possibly
other) codes have an extra single letter after the first number - e.g.
EC1S 4JJ) and then putting the requisite "bits" together to create
your larger and smaller areas. You could do it with lots of nested
IIF()s and Mid()s, but I wouldn't!

On Wed, 9 Jun 2004 14:57:49 +0100, "Lapchien"
wrote:

I need to split the UK postcodes in my db down to 4 types. For example,
the
original POSTCODE field of AB10 6YT needs to become :

AB
AB10
AB10 6

In the UK we use postcodes of slightly 'unequal' length, so we could have
AB10 6YT or AB1 6YT.

What query could I use to accomplish this?

Thanks,
Lap



Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

That is obviously only intended to produce the first alpha sextion of
the postcode - the widest area. Unless you are _100%_ sure that
strCode will never be empty or "malformed", you should have a test for
strCode being a Null String at the beginning of the Function and an
exit from the Do loop if you run out of characters before finding a
numeric one.

Also, if you are sure that you a handling strings, it is slightly more
efficient to use Mid$, but that is not your problem.

On Thu, 10 Jun 2004 07:36:08 +0100, "Lapchien"
wrote:

Well, this is what I am using -

Function ShortPO(strCode As String) As String
Dim strHold As String
Dim LongCnt As Long

LongCnt = 1

Do Until IsNumeric(Mid(strCode, LongCnt, 1))
strHold = strHold & Mid(strCode, LongCnt, 1)
LongCnt = LongCnt + 1
Loop
ShortPO = strHold

End Function

But When I run my query, although it returns the first part of the post code
I get out of memory errors...

Anyone have any thoughts?

Lap



"Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in message
news
I would do it in VBA by parsing the postcode into the two numeric and
two alpha components (remembering that some London (and possibly
other) codes have an extra single letter after the first number - e.g.
EC1S 4JJ) and then putting the requisite "bits" together to create
your larger and smaller areas. You could do it with lots of nested
IIF()s and Mid()s, but I wouldn't!

On Wed, 9 Jun 2004 14:57:49 +0100, "Lapchien"
wrote:

I need to split the UK postcodes in my db down to 4 types. For example,
the
original POSTCODE field of AB10 6YT needs to become :

AB
AB10
AB10 6

In the UK we use postcodes of slightly 'unequal' length, so we could have
AB10 6YT or AB1 6YT.

What query could I use to accomplish this?

Thanks,
Lap



Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---



Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Thanks. Care to offer some code!?

Lap


"Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in message
...
That is obviously only intended to produce the first alpha sextion of
the postcode - the widest area. Unless you are _100%_ sure that
strCode will never be empty or "malformed", you should have a test for
strCode being a Null String at the beginning of the Function and an
exit from the Do loop if you run out of characters before finding a
numeric one.

Also, if you are sure that you a handling strings, it is slightly more
efficient to use Mid$, but that is not your problem.

On Thu, 10 Jun 2004 07:36:08 +0100, "Lapchien"
wrote:

Well, this is what I am using -

Function ShortPO(strCode As String) As String
Dim strHold As String
Dim LongCnt As Long

LongCnt = 1

Do Until IsNumeric(Mid(strCode, LongCnt, 1))
strHold = strHold & Mid(strCode, LongCnt, 1)
LongCnt = LongCnt + 1
Loop
ShortPO = strHold

End Function

But When I run my query, although it returns the first part of the post
code
I get out of memory errors...

Anyone have any thoughts?

Lap



"Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in message
news
I would do it in VBA by parsing the postcode into the two numeric and
two alpha components (remembering that some London (and possibly
other) codes have an extra single letter after the first number - e.g.
EC1S 4JJ) and then putting the requisite "bits" together to create
your larger and smaller areas. You could do it with lots of nested
IIF()s and Mid()s, but I wouldn't!

On Wed, 9 Jun 2004 14:57:49 +0100, "Lapchien"
wrote:

I need to split the UK postcodes in my db down to 4 types. For
example,
the
original POSTCODE field of AB10 6YT needs to become :

AB
AB10
AB10 6

In the UK we use postcodes of slightly 'unequal' length, so we could
have
AB10 6YT or AB1 6YT.

What query could I use to accomplish this?

Thanks,
Lap



Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---



Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---

I would do it like this - considerably less efficient than your code
for real postcodes, but much more tolerant of invalid postcodes.

As long as the first part of the Postcode is valid, the following two
functions will return (respectively) its alpha and integer components.
Most invalid codes will return the Null string and -1 (again
respectively). I think that I have handled all possible bad arguments.
I have gone for clarity rather than speed, but the Functions should
not be too inefficient. You can do the same sort of thing for the
second half of the postcode. Note that I am passing the PostCode as a
Variant. If you are _100%_ sure that you will always be passing a
string, you can save a bit of code and time by declaring it as such
and deleting the two lines with the '** comments.

Function AlphaPart(PostCode As Variant) As String
' Returns the alpha part of the first "block" of a UK Postcode
' a null string is the error return

Dim strTemp As String
Dim strReturn As String
Dim blDone As Boolean

AlphaPart = vbNullString
blDone = False

If IsNull(PostCode) Then Exit Function '**
If VarType(PostCode) vbString Then Exit Function '**
If Len(PostCode) 2 Then Exit Function

' Deal with likely bad arguments

strTemp = UCase(PostCode) ' just in case it wasn't

strReturn = Mid$(strTemp, 1, 1)

Select Case strReturn
Case "A" To "Z"
' that's OK, continue
Case Else
Exit Function ' not a valid postcode
End Select

Select Case Mid$(strTemp, 2, 1)
Case "A" To "Z"
strReturn = strReturn & Mid$(strTemp, 2, 1)
Case "0" To "9"
blDone = True
Case Else
Exit Function ' not a valid postcode
End Select

If blDone Then
AlphaPart = strReturn
Else
Select Case Mid$(strTemp, 3, 1)
Case "0" To "9"
AlphaPart = strReturn
Case Else
' not a valid postcode
End Select
End If

End Function


Function NumPart(PostCode As Variant) As Integer
' Returns the numeric part of the first "block" of a UK Postcode as an
integer
' -1 is the error return

Dim strTemp As String
Dim intReturn As Integer
Dim blDone As Boolean

NumPart = -1
blDone = False

If IsNull(PostCode) Then Exit Function '**
If VarType(PostCode) vbString Then Exit Function '**
If Len(PostCode) 2 Then Exit Function

strTemp = UCase(PostCode)

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
Exit Function ' invalid postcode
Case "A" To "Z"
strTemp = Mid$(strTemp, 2)
Case Else
Exit Function ' invalid postcode
End Select

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
' start of the numeric part
Case "A" To "Z"
strTemp = Mid$(strTemp, 2)
Case Else
Exit Function ' invalid postcode
End Select

' now we should be at the start of the numeric part

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
intReturn = CInt(Mid$(strTemp, 1, 1))
strTemp = Mid$(strTemp, 2)
Case Else
Exit Function ' invalid postcode
End Select

' is there another digit?

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
intReturn = intReturn * 10 + CInt(Mid$(strTemp, 1, 1))
Case Else
blDone = True
End Select

If blDone Then
NumPart = intReturn
Else
Select Case Mid$(strTemp, 2, 1)
Case "0" To "9" ' there shouldn't be another!
' not a valid postcode
Case Else
NumPart = intReturn
End Select
End If

End Function

End Function


On Thu, 10 Jun 2004 11:20:43 +0100, "Lapchien"
wrote:

Thanks. Care to offer some code!?

Lap


"Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in message
.. .
That is obviously only intended to produce the first alpha sextion of
the postcode - the widest area. Unless you are _100%_ sure that
strCode will never be empty or "malformed", you should have a test for
strCode being a Null String at the beginning of the Function and an
exit from the Do loop if you run out of characters before finding a
numeric one.

Also, if you are sure that you a handling strings, it is slightly more
efficient to use Mid$, but that is not your problem.

On Thu, 10 Jun 2004 07:36:08 +0100, "Lapchien"
wrote:

Well, this is what I am using -

Function ShortPO(strCode As String) As String
Dim strHold As String
Dim LongCnt As Long

LongCnt = 1

Do Until IsNumeric(Mid(strCode, LongCnt, 1))
strHold = strHold & Mid(strCode, LongCnt, 1)
LongCnt = LongCnt + 1
Loop
ShortPO = strHold

End Function

But When I run my query, although it returns the first part of the post
code
I get out of memory errors...

Anyone have any thoughts?

Lap



"Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in message
news I would do it in VBA by parsing the postcode into the two numeric and
two alpha components (remembering that some London (and possibly
other) codes have an extra single letter after the first number - e.g.
EC1S 4JJ) and then putting the requisite "bits" together to create
your larger and smaller areas. You could do it with lots of nested
IIF()s and Mid()s, but I wouldn't!

On Wed, 9 Jun 2004 14:57:49 +0100, "Lapchien"
wrote:

I need to split the UK postcodes in my db down to 4 types. For
example,
the
original POSTCODE field of AB10 6YT needs to become :

AB
AB10
AB10 6

In the UK we use postcodes of slightly 'unequal' length, so we could
have
AB10 6YT or AB1 6YT.

What query could I use to accomplish this?

Thanks,
Lap



Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---



Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---



Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Many thanks

"Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in message
...
I would do it like this - considerably less efficient than your code
for real postcodes, but much more tolerant of invalid postcodes.

As long as the first part of the Postcode is valid, the following two
functions will return (respectively) its alpha and integer components.
Most invalid codes will return the Null string and -1 (again
respectively). I think that I have handled all possible bad arguments.
I have gone for clarity rather than speed, but the Functions should
not be too inefficient. You can do the same sort of thing for the
second half of the postcode. Note that I am passing the PostCode as a
Variant. If you are _100%_ sure that you will always be passing a
string, you can save a bit of code and time by declaring it as such
and deleting the two lines with the '** comments.

Function AlphaPart(PostCode As Variant) As String
' Returns the alpha part of the first "block" of a UK Postcode
' a null string is the error return

Dim strTemp As String
Dim strReturn As String
Dim blDone As Boolean

AlphaPart = vbNullString
blDone = False

If IsNull(PostCode) Then Exit Function '**
If VarType(PostCode) vbString Then Exit Function '**
If Len(PostCode) 2 Then Exit Function

' Deal with likely bad arguments

strTemp = UCase(PostCode) ' just in case it wasn't

strReturn = Mid$(strTemp, 1, 1)

Select Case strReturn
Case "A" To "Z"
' that's OK, continue
Case Else
Exit Function ' not a valid postcode
End Select

Select Case Mid$(strTemp, 2, 1)
Case "A" To "Z"
strReturn = strReturn & Mid$(strTemp, 2, 1)
Case "0" To "9"
blDone = True
Case Else
Exit Function ' not a valid postcode
End Select

If blDone Then
AlphaPart = strReturn
Else
Select Case Mid$(strTemp, 3, 1)
Case "0" To "9"
AlphaPart = strReturn
Case Else
' not a valid postcode
End Select
End If

End Function


Function NumPart(PostCode As Variant) As Integer
' Returns the numeric part of the first "block" of a UK Postcode as an
integer
' -1 is the error return

Dim strTemp As String
Dim intReturn As Integer
Dim blDone As Boolean

NumPart = -1
blDone = False

If IsNull(PostCode) Then Exit Function '**
If VarType(PostCode) vbString Then Exit Function '**
If Len(PostCode) 2 Then Exit Function

strTemp = UCase(PostCode)

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
Exit Function ' invalid postcode
Case "A" To "Z"
strTemp = Mid$(strTemp, 2)
Case Else
Exit Function ' invalid postcode
End Select

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
' start of the numeric part
Case "A" To "Z"
strTemp = Mid$(strTemp, 2)
Case Else
Exit Function ' invalid postcode
End Select

' now we should be at the start of the numeric part

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
intReturn = CInt(Mid$(strTemp, 1, 1))
strTemp = Mid$(strTemp, 2)
Case Else
Exit Function ' invalid postcode
End Select

' is there another digit?

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
intReturn = intReturn * 10 + CInt(Mid$(strTemp, 1, 1))
Case Else
blDone = True
End Select

If blDone Then
NumPart = intReturn
Else
Select Case Mid$(strTemp, 2, 1)
Case "0" To "9" ' there shouldn't be another!
' not a valid postcode
Case Else
NumPart = intReturn
End Select
End If

End Function

End Function


On Thu, 10 Jun 2004 11:20:43 +0100, "Lapchien"
wrote:

Thanks. Care to offer some code!?

Lap


"Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in message
.. .
That is obviously only intended to produce the first alpha sextion of
the postcode - the widest area. Unless you are _100%_ sure that
strCode will never be empty or "malformed", you should have a test for
strCode being a Null String at the beginning of the Function and an
exit from the Do loop if you run out of characters before finding a
numeric one.

Also, if you are sure that you a handling strings, it is slightly more
efficient to use Mid$, but that is not your problem.

On Thu, 10 Jun 2004 07:36:08 +0100, "Lapchien"
wrote:

Well, this is what I am using -

Function ShortPO(strCode As String) As String
Dim strHold As String
Dim LongCnt As Long

LongCnt = 1

Do Until IsNumeric(Mid(strCode, LongCnt, 1))
strHold = strHold & Mid(strCode, LongCnt, 1)
LongCnt = LongCnt + 1
Loop
ShortPO = strHold

End Function

But When I run my query, although it returns the first part of the
post
code
I get out of memory errors...

Anyone have any thoughts?

Lap



"Peter R. Fletcher" pfletch(at)fletchers(hyphen)uk.com wrote in
message
news I would do it in VBA by parsing the postcode into the two numeric
and
two alpha components (remembering that some London (and possibly
other) codes have an extra single letter after the first number -
e.g.
EC1S 4JJ) and then putting the requisite "bits" together to create
your larger and smaller areas. You could do it with lots of nested
IIF()s and Mid()s, but I wouldn't!

On Wed, 9 Jun 2004 14:57:49 +0100, "Lapchien"
wrote:

I need to split the UK postcodes in my db down to 4 types. For
example,
the
original POSTCODE field of AB10 6YT needs to become :

AB
AB10
AB10 6

In the UK we use postcodes of slightly 'unequal' length, so we
could
have
AB10 6YT or AB1 6YT.

What query could I use to accomplish this?

Thanks,
Lap



Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World!
100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---



Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption
=---



Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---