postcodes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi everyone

festive greetings to you all

im working on a client contact database, and im trying to count how many
contacts i have in postal regions

best way i feel for this is using the postcode field eg G81 6NT, or EH2 4BW

what i was wondering is how can i select out the G or EH allowing me to use
a simple group and count query (that bit i can do)

thanks

stuart
 
Stuart

Have you run across the "Like" operator in your selection criterion, plus
using a Totals query?
 
hi everyone

festive greetings to you all

im working on a client contact database, and im trying to count how many
contacts i have in postal regions

best way i feel for this is using the postcode field eg G81 6NT, or EH2 4BW

what i was wondering is how can i select out the G or EH allowing me to use
a simple group and count query (that bit i can do)

thanks

stuart
Click to expand...

To group by the first byte (G or E): create a calculated field in the
Query by typing

Region: Left([Postcode], 1)

in a vacant Field cell. Group By this field in your totals query.

Left([Postcode], 2) will of course extract the first two bytes.

If you want to extract the first N bytes up to the first numeric
character, it gets a bit more complex - I'm not familiar enough with
the (British??) Postcode system to advise specifically.

John W. Vinson[MVP]
 
Something like this, assuming that there are no international postcodes
in the database that also start with letter-number or
letter-letter-number:

Public Function GetPostCodeArea2(V As Variant) As Variant
Dim S As String
GetPostCodeArea2 = Null
If IsNull(V) Then Exit Function

S = CStr(V)
If S Like "[A-Z]#*" Then
GetPostCodeArea2 = Left(S, 1)
ElseIf S Like "[A-Z][A-Z]#*" Then
GetPostCodeArea2 = Left(S, 2)
End If
End Function
 
Dear Stuart:

Your question has brought some thoughtful and expert opinions, but this has
me wondering if I have a grasp of what you want.

The problem, it would seem, is that you need to be able to GROUP the results
by a "sub-field" of the postal code that is at least variable in length.
Using your sample data, the sub-field of interest is:

G81 G
EH4 EH

But for the other postal codes, 6NT and 4BW, what is the sub-field of
interest? Is it NT and BW? Is the rule for extracting this code perhaps to
first ignore all the initial digits, then take all the consecutive letters?
Or is it something else. That is:

6NT NT
4BW BW

If my guess is anywhere close, then John Nurick's solution is at least a
start. The first step, in any case, is to have a function that reliably
extacts the desired code, whether it is written within your query or as a
separate public function which can be referenced by your query. In either
case, a query should be used to test the accuracy of this function before
proceeding, that is a query based on your existing data and giving results
in just 2 columns like what I have shown above.

Once you have that working, the rest should be fairly easy. So, making sure
you have transmitted to us just what these rules amount to would seem to be
paramount.

Tom Ellison
 
Dear Tom,
If I recall correctly, the postal codes listed use a space as a separator.
Similar to the US using a dash for ZIP plus 4. So G81 6NT is one postal
code.

Sincerely,
John
 
Dear John,

That makes sense. So, John Nurick's proposed code would seem to handle
everything.

Well, I won't propose that I'm an expert on foreign postal codes!

Tom Ellison
 
Dear Doug,

Well, I certainly have no unblemished record myself. Please promise me you
won't be shocked the next time I screw up! So, I won't try to create any
unreasonably high expectations for others. But I'm certainly pleased that
John seems to know everything about postal codes throughout the world.

Tom Ellison
 
<blush>
Living in the UK I need to know about our strange but effective system.
FWIW a search at www.royalmail.com shows that the postcode G81 6NT is
shared by ten houses in one street in the town of Clydebank near
Glasgow.

For other postcodes and addresses, I start at Frank's Compulsive Guide
to Postal Addresses at http://www.columbia.edu/kermit/postal.html.
</blush>

But I'm certainly pleased that
John seems to know everything about postal codes throughout the world.
 
Back
Top