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.

 

[Uncle Gizmo]

There is an example on my website of how to extract UK postcodes From text.

See item 1

You are welcome to adapt that to your requirements. I may be able to help adapt it, work comitments permitting....

The part of the postcode you are trying to work with the called the outward code. and you have discovered the problem, in that this part of the code is of variable lengh.

To overcome this problem, my code first detects the "Inward Codes" position in the text and uses that to find the outward code.

 

[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.

 

[Uncle Gizmo]

Yes , I looked at My code too, and decided it would be easier to start from scratch!

Please see attached.

 

[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.