separating postcodes in a query

[Matrix]

Hi,

Could anyone help me. I am trying to separate a list of postcodes in a query just to extract the letters of the first part postcodes (i.e BS, NE, HW etc). The only problem is that all the postcodes are of different lengths and so if I just extract the first left 2 charcs this will give me a number also where there is only one letter and not two (i.e B11 gives B1).

Does anyone know of an expression which can check the second letter/number and if it is a number then to exclude it. Maybe an IIF statement??

Many Thanks.

 

[Matrix]

I have tried this from downloading your postcodes database and used your "LocateOutward" function, but it still returns the same result.

I need only to extract the letters in the outcode (i.e. NE, BS or just W if it is W3).

I do not have enought VBA knowledge to adapt your code.

Thanks.

 

[Brianwarnock]

Would something as simple as using a mid and a where solve your problem.

Use the Left([fldpostcode],2) to select the 2 first characters but also Mid([fldpostcode],2,1) to select only the 2nd and in the criteria put between "a" and "z"

The SQL will look something like
SELECT Table.pcode, Left([fldpostcode],2) AS PC2, Mid([fldpostcode],2,1) AS pc12
FROM Table
WHERE Mid([fldpostcode],2,1) Between "a" And "z";



Brian

 

[Matrix]

Uncle Gizmo,

That function you have written works a wonder ! That's just what I wanted and in time for the boaring meeting I have to go to now !

Thanks.