you need to use VBA code in the textbox's
BeforeUpdate event tovalidatethe 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})"
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:
[ANSI-92 query mode wildcard characters]
CREATE TABLE UKPostalAddresses (
postcode VARCHAR(8),
CONSTRAINT uk_postcode__pattern
CHECK
(
postcode LIKE '[A-Z][0-9] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][0-9][0-9] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]'
),
CONSTRAINT uk_postcode__invalid_chars_pos_1
CHECK (postcode NOT LIKE '[QVX]%'),
CONSTRAINT uk_postcode__invalid_chars_pos_2
CHECK (postcode NOT LIKE '_[IJZ]%'),
CONSTRAINT uk_postcode__valid_chars_pos_3
CHECK
(
1 = IIF(postcode LIKE '[A-Z][0-9][A-Z]%',
IIF(postcode LIKE '[A-Z][0-9][ABCDEFGHJKSTUW]%', 1, 0), 1)
),
CONSTRAINT uk_postcode__valid_chars_pos_4
CHECK
(
1 = IIF(postcode LIKE '[A-Z][A-Z][0-9][A-Z]%',
IIF(postcode LIKE '[A-Z][A-Z][0-9][ABEHMNPRVWXY]%', 1, 0), 1)
),
CONSTRAINT uk_postcode__valid_chars_inward_part
CHECK
(
postcode NOT LIKE '%[CIKMOV]_'
AND postcode NOT LIKE '%[CIKMOV]'
)
);
Jamie.
--
