Access Table Input Masks

[WilliamE]

I have a database table with one of the fields being postcode. Access
originally sets this up as the US Postal Code I can change it to the UK
Postcode Format However since in the UK The following formats are used viz.
LL0 0LL OR L00 0LL I find it impossible to get both of these to work in the
same field . I beleive there must be a way but just cant think how to do it.
any idea of the way to do this would be greatfully received

 

[Jeff L]

Try LA0 0LL
The A means it can be a letter or a digit.

Hope that helps!

 

[WilliamE]

Try LA0 0LL
The A means it can be a letter or a digit.

Hope that helps!

Thanks Mate works a treat.

 

[David F Cox]

did not work for me with postcode MK40 1NG

Thanks Mate works a treat.

 

[David F Cox]

and a memory cell clicked on - the input mask wizard has the right answer:

LL00 0LL;0;_

with an alternative if you do not want the space saved.

 

[John Vinson]

did not work for me with postcode MK40 1NG

Nope. Need to persuade Her Majesty's Post to come into the 21st
century. Good luck....

AFAIK there's no way to do this with an input mask, they're just not
flexible enough; you need to use VBA code in the textbox's
BeforeUpdate event to validate the format.

John W. Vinson[MVP]

 

[Tim Ferguson]

AFAIK there's no way to do this with an input mask, they're just not
flexible enough; you need to use VBA code in the textbox's
BeforeUpdate event to validate the format.

I generally don't bother, but this is the reference that I keep in my
archive:

http://www.access-programmers.co.uk/forums/showthread.php?t=97666


'Official formatting of postcodes and the like may change
'over time. Some of these expressions may need adjustment
' to bring them up to date.
'UK Postcode
Public Const rgxZIP_UK = "(??:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})"
'A simpler expression that does not check for valid postcode areas:
' "(?:[A-Z]{1,2}\d(?:\d|[A-Z])? \d[A-Z]{2})"


There is similar code here: ->
http://www.mvps.org/access/modules/mdl0063.htm


There is more information at

http://www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm
http://www.evoxfacilities.co.uk/evoxps.htm

Hope that helps


Tim F

 

[Tim Ferguson]

A few ideas: constraints are best in the database; multiple simple
rules are better than one complex rule e.g. improved granularity of
error message:

Trouble is (a) not all postcodes that fit these patterns are valid; and (b)
there is every likelihood that new valid postcodes will be created in the
future.

My own view is that you either do the thing properly, i.e. validate the
address itself against the current PAF (post office address file), which
requires a subscription, or not at all. YMMV.


All the best


Tim