UK Post Code Checking

[Simon]

Is there any code that will make sure the letter in the post code are
automaticlay turned into Upercase.

Also is there any code to verify its a corect Post code format

Thanks

simon

 

[onedaywhen]

is there any code to verify its a corect Post code format

Try this (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.

--

 

[Douglas J. Steele]

You can use the UCase function in the AfterUpdate event to convert the text
to upper case.

Take a look at http://access.mvps.org/modules/mdl0063.htm at "The Access
Web" for one approach to verifying its format.