I need to summarise by the first part of the UK postcode, which signifies the city, area or London postal area. Unfortunately, this is either 1 or 2 letters long (the postcode system was not designed with database designers in mind!)Examples of post codes are CW4 7QG, SY3 8UJ, S4 5RF. I basically need the first 2 characters if they are both letters and only the first character if the second character is a number.

I first of all defined an intermediary field, call it PInter, as ;

TextToNum(Right(Left(Postcode,2),1))

This should return a number if a number is the second character and be empty if not. This field would be used to define the PostcodeSummary field as either the 1st or 1st and 2nd characters accordingly:

If(IsEmpty(PInter),Left(Postcode,2),Left(Postcode,1))

(I then combined both fields into one)

This appeared to work just fine, until I checked data and saw a problem. The calculated field PInter returns 1 if the second character is either a "Y" or a "T", where I want it to return a blank as the second character is not a number but a letter(It's presumably to to do with logic (?))

Any suggestions for getting round this ?

Andrew,

Try this instead

If( Position( postcode, " ", 1, 1) = 4, 2, 1)

However, what about the West End. W1W 8HR :?

Anyhow.

HTH

Try this one...

Postcode_Left_Character =

code:

---

Left(Postcode,1)&If(Middle(Postcode, 2, 1) >= "A" and Middle(Postcode, 2, 1) <= "Z",Middle(Postcode, 2, 1),"")

Russ Baker

Canberra, Australia

Create a calc field and use it to summarize on:

code:

---

If(Middle(PostalCode, 2, 1) >= 0 and Middle(PostalCode, 2, 1) <= 9, Left(PostalCode, 1), Left(PostalCode, 2))

I like simple solutions, I do.