If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
UK postcode issue
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 =--- |
#3
|
|||
|
|||
UK postcode issue
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 =--- |
#4
|
|||
|
|||
UK postcode issue
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 =--- |
#5
|
|||
|
|||
UK postcode issue
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 =--- |
#6
|
|||
|
|||
UK postcode issue
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 =--- |
#7
|
|||
|
|||
UK postcode issue
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 =--- |
Thread Tools | |
Display Modes | |
|
|