Query based on Format of a field

[jempie]

All,

I have a Postcode field in my table and I want to be able to check the data to make sure that it is a correct UK postcode.

Is there away that I can find out the format of the data, to be able to run a query against it, or is there a better way of doing it?

I need to account for all types of UK postcodes (A1 2BC, A12 3BC, AB12 3CD, WC2A 3BC). There are also foreign postcodes in this field.

Help appreciated!

 

[neileg]

Mmm...

You can do a few tests. You can use InStr() to find the position of the space. If it is not 3, 4 or 5, then it's not a UK postcode. You can get the length of the code using Len() and subtract the the position of the space. The answer should be 3 as the second block of characters should be 3 characters.

You can use Asc() and Left() to return the ASCII code of the first character or Asc() and Right() to get the last two. Capital letters are between 65 and 90 so outside of that it's not a valid character in those positions.

You can use IsNumeric() and InStr()to check that the first character after the space is a number.

I think that's about all you can do. You can have a string of characters that obeys all the rules of a post code, but it's still not a valid post code. You'll never catch that one unless you have a continually updated table of current post codes.

 

[jempie]

I feared there would be no definitive way of checking.

Thanks for all that info though Neil, it will help me.