Use Replace function to reduce postcode to postcode region

[mark]

I have a table of UK companies whose records I want to filter using a
map of postcode regions.

For the benfit of people outside the UK, our postcodes are a pain to
work with because they are not a standard length, and the part which
identifies the region isn't a standard length either.

The first letters of the postcode denotes a general region, and then
sub-regions are denoted by the following numbers and letters.

Thus a valid postcode could be

GL24 6TY
G1 4TH

Which means you can't simply Trim Left to the first two characters and
then filter the results on a region code of, say "G", because you would

be returning results from Glasgow and Gloucester.

If I can express each postcode in my table as a region (that is, the
first letters of the postcode only), I could then filter this query
using variables passed from my map (the variables will simply be the
postcode region letters, eg. G, L, M, PL, ME.

To do this I guess I need to take everything from the first occurrence
of a number in my postcode and get rid of it.

Is there a way of using the Replace function in an Access query to do
this?

 

[Marshall Barton]

I have a table of UK companies whose records I want to filter using a
map of postcode regions.

For the benfit of people outside the UK, our postcodes are a pain to
work with because they are not a standard length, and the part which
identifies the region isn't a standard length either.

The first letters of the postcode denotes a general region, and then
sub-regions are denoted by the following numbers and letters.

Thus a valid postcode could be

GL24 6TY
G1 4TH

Which means you can't simply Trim Left to the first two characters and
then filter the results on a region code of, say "G", because you would

be returning results from Glasgow and Gloucester.

If I can express each postcode in my table as a region (that is, the
first letters of the postcode only), I could then filter this query
using variables passed from my map (the variables will simply be the
postcode region letters, eg. G, L, M, PL, ME.

To do this I guess I need to take everything from the first occurrence
of a number in my postcode and get rid of it.

Is there a way of using the Replace function in an Access query to do
this?

No. Replace can not do that.

If the region part is always just one or two alphabetic
characters, you could use:

IIf(postcode LIKE "?#*", Left(postcode,1), Left(postcode,2))

OTOH, if the region letters can be three or more characters,
the expression will quickly become very messy. If it gets
too messy to be practical, I suggest that you create a user
defined function to deal with the ideosyncracies.

 

[John Nurick]

Hi Marsh,

IIf(postcode LIKE "?#*", Left(postcode,1), Left(postcode,2))

That should work for all ordinary UK postcodes.

 

[raskew via AccessMonster.com]

Hi -

The following will return the leftmost alpha characters in a string,
regardless of the number of characters:

Function fSaveFirstAlpha(pPostCode As String) As String
'*******************************************
'Purpose:   Return the first nth contiguous
'           alpha characters in a string
'Coded by:  raskew
'Inputs: (1) ? fSaveFirstAlpha("GL24 6TY")
'           (2) ? fSaveFirstAlpha("G1 4TH")
'           (3) ? fSaveFirstAlpha("GABC1 4TH")
'           (4) ? fSaveFirstAlpha("4G1 4TH")

'Outputs:   (1) GL
'                (2) G
'                (3) GABC
'                (4)
'*******************************************

Dim i       As Integer
Dim n       As Integer
Dim strHold As String


strHold = pPostCode
n = Len(strHold)
i = 1
Do While Asc(Mid(strHold, i, 1)) >= 65 And Asc(Mid(strHold, i, 1)) <= 90
And i <= n
i = i + 1
Loop
fSaveFirstAlpha = Mid(strHold, 1, i - 1)

End Function

HTH - Bob

 

[Gelatinous_Blob]

Thanks Marsh, that looks like a neat solution...

[apols for double posting BTW]

 

[Gelatinous_Blob]

I tried:

IIf(postcode LIKE "?#*", Left(postcode,1), Left(postcode,2))

This appears to work in query results but on closer inspection it seems
to produce an underscore after single letter postcode areas. Thus G2
6TH is shortened to G_.

Any ideas why that should be the case?

Thanks!

Mark

 

[Marshall Barton]

Thanks Marsh, that looks like a neat solution...

Happy to help.

[apols for double posting BTW]

Live and learn.

 

[Marshall Barton]

That code can not add extraneous characters.

Is it possible that the underscore was in the post code? If
not, then maybe there's some other stuff going on that is
adding or just displaying the extra character. Do you have
an input mask set on the text box used to display the value
from the query? What about the Format property?

 

[John Nurick]

I'd guess an input mask. Some versions of Access (at least with UK
regional settings) have an "Postal Code" input mask that was designed by
someone who'd seen UK postcodes but didn't understand them. There are
many valid postcodes that it won't accept!

 

[Gelatinous_Blob]

John, you were right, an input mask had got in there somehow.

Removing it had the desired effect of reducing all the postcodes to one
or two characters.

But when I apply this to an ASP page, for some reason my recordset only
returns matches against two-character postcode areas.

So if my query string value is EH, that's fine, everything in the
Edinburgh area is returned, but if it's G, the recordset comes back
empty.

This just seems plain weird to me, but no doubt I've missed something
obvious. Any ideas? Here's the code for the recordset.

<%
Dim RSretailers
Dim RSretailers_numRows

Set RSretailers = Server.CreateObject("ADODB.Recordset")
RSretailers.ActiveConnection = MM_cid_conn_STRING
RSretailers.Source = "SELECT BusinessCity, BusinessCountry,
BusinessPhone, BusinessPostalCode, BusinessState, BusinessStreet,
BusinessCompany, BusinessID, BusinessType, BusinessWebPage FROM
Retailers WHERE IIf([BusinessPostalCode] Like
""?#*"",Left([BusinessPostalCode],1),Left([BusinessPostalCode],2)) = '"
+ Request.QueryString("postcode") + "' ORDER BY BusinessCity ASC,
Company ASC"
RSretailers.CursorType = 0
RSretailers.CursorLocation = 2
RSretailers.LockType = 1
RSretailers.Open()

RSretailers_numRows = 0
%>

I'd guess an input mask. Some versions of Access (at least with UK
regional settings) have an "Postal Code" input mask that was designed by
someone who'd seen UK postcodes but didn't understand them. There are
many valid postcodes that it won't accept!

That code can not add extraneous characters.

Is it possible that the underscore was in the post code? If
not, then maybe there's some other stuff going on that is
adding or just displaying the extra character. Do you have
an input mask set on the text box used to display the value
from the query? What about the Format property?--

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[John Nurick]

I've never used ASP, but at a guess it's a question of using ANSI SQL
wildcard characters rather than the Access norm of Jet SQL. From Help:

Matching character Microsoft Jet SQL ANSI SQL
Any single character ? _ (underscore)
Zero or more characters * %

So try changing
Like ""?#*""
to
Like ""_#%""

John, you were right, an input mask had got in there somehow.

Removing it had the desired effect of reducing all the postcodes to one
or two characters.

But when I apply this to an ASP page, for some reason my recordset only
returns matches against two-character postcode areas.

So if my query string value is EH, that's fine, everything in the
Edinburgh area is returned, but if it's G, the recordset comes back
empty.

This just seems plain weird to me, but no doubt I've missed something
obvious. Any ideas? Here's the code for the recordset.

<%
Dim RSretailers
Dim RSretailers_numRows

Set RSretailers = Server.CreateObject("ADODB.Recordset")
RSretailers.ActiveConnection = MM_cid_conn_STRING
RSretailers.Source = "SELECT BusinessCity, BusinessCountry,
BusinessPhone, BusinessPostalCode, BusinessState, BusinessStreet,
BusinessCompany, BusinessID, BusinessType, BusinessWebPage FROM
Retailers WHERE IIf([BusinessPostalCode] Like
""?#*"",Left([BusinessPostalCode],1),Left([BusinessPostalCode],2)) = '"
+ Request.QueryString("postcode") + "' ORDER BY BusinessCity ASC,
Company ASC"
RSretailers.CursorType = 0
RSretailers.CursorLocation = 2
RSretailers.LockType = 1
RSretailers.Open()

RSretailers_numRows = 0
%>

I'd guess an input mask. Some versions of Access (at least with UK
regional settings) have an "Postal Code" input mask that was designed by
someone who'd seen UK postcodes but didn't understand them. There are
many valid postcodes that it won't accept!

That code can not add extraneous characters.

Is it possible that the underscore was in the post code? If
not, then maybe there's some other stuff going on that is
adding or just displaying the extra character. Do you have
an input mask set on the text box used to display the value
from the query? What about the Format property?--

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[Gelatinous_Blob]

Got my hopes up there, but no joy. Tried all three wild cards but this
doesn't seem to do the trick.

One curious thing:

Records successfully filter on single character numerical values, but
not single character text values. It does however work on double
character text values.

I think I'll take this on to an ASP group and see what I come up with.
I'll post any resolution here for anyone interested.

Thanks for your help so far.

I've never used ASP, but at a guess it's a question of using ANSI SQL
wildcard characters rather than the Access norm of Jet SQL. From Help:

Matching character Microsoft Jet SQL ANSI SQL
Any single character ? _ (underscore)
Zero or more characters * %

So try changing
Like ""?#*""
to
Like ""_#%""

John, you were right, an input mask had got in there somehow.

Removing it had the desired effect of reducing all the postcodes to one
or two characters.

But when I apply this to an ASP page, for some reason my recordset only
returns matches against two-character postcode areas.

So if my query string value is EH, that's fine, everything in the
Edinburgh area is returned, but if it's G, the recordset comes back
empty.

This just seems plain weird to me, but no doubt I've missed something
obvious. Any ideas? Here's the code for the recordset.

<%
Dim RSretailers
Dim RSretailers_numRows

Set RSretailers = Server.CreateObject("ADODB.Recordset")
RSretailers.ActiveConnection = MM_cid_conn_STRING
RSretailers.Source = "SELECT BusinessCity, BusinessCountry,
BusinessPhone, BusinessPostalCode, BusinessState, BusinessStreet,
BusinessCompany, BusinessID, BusinessType, BusinessWebPage FROM
Retailers WHERE IIf([BusinessPostalCode] Like
""?#*"",Left([BusinessPostalCode],1),Left([BusinessPostalCode],2)) = '"
+ Request.QueryString("postcode") + "' ORDER BY BusinessCity ASC,
Company ASC"
RSretailers.CursorType = 0
RSretailers.CursorLocation = 2
RSretailers.LockType = 1
RSretailers.Open()

RSretailers_numRows = 0
%>

I'd guess an input mask. Some versions of Access (at least with UK
regional settings) have an "Postal Code" input mask that was designed by
someone who'd seen UK postcodes but didn't understand them. There are
many valid postcodes that it won't accept!
On Thu, 18 Jan 2007 11:56:53 -0600, Marshall Barton
That code can not add extraneous characters.
Is it possible that the underscore was in the post code? If
not, then maybe there's some other stuff going on that is
adding or just displaying the extra character. Do you have
an input mask set on the text box used to display the value
from the query? What about the Format property?--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.--

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.- Hide quoted text -- Show quoted text -