Format for UK Postcode input into table

[Kassy]

I guess this is pretty fundamental but I cant get it right! I want to search for a member by post code so I need to have validation for my table field post code so that when my parameter query accepts the post code input records will be returned covering all input types e.g. Users can put in CF72 (space)9AD or CF729AD and still get the same records or alternately how do I make users enter Post Codes with the correct UK spacing i.e 4 characters a space then the remaining characters?

 

[ByteMyzer]

Set the Input Mask for your Zip Code field to AA00\ 0AA. (Note the space between the \ and the 0)

 

[Kassy]

UK Zip Code

Many thanks sounds good. I guessed it was something like that butkept getting the 'space bit' wrong- will try it.

 

[Bat17]

Post Codes with the correct UK spacing i.e 4 characters a space then the remaining characters?

UK codes just ar'nt that friendly

You can certainly get EN8 9SL and I think London uses SW1 still.

Might do better to strip the spaces from the data in a query and search againts a criteria that has had its spaces stripped as well.

peter

 

[Digby]

Have to agree with above I deal with post codes everyday and they are not friendly at all, you can get standard AA00 0AA and also AA0 0AA i.e. SW1 3AP you can even get AA0A 0AA and Im not joking, SW1V 3PP, thats Dolphin Square, if you know London?

Any way as with the input mask I do not know how to get round it but just thought I would make you aware. Good luck

 

[Kassy]

Can see a problem

I can see the problem however my database is based mainly locally because it is a rugby club for our immediate vicinity and most members live local to the club so fingers crossed I wont need too many other area codes that will confuse the issue- I wouldn't have a clue how to go about stripping the spaces Im only just starting out and thats basically using the wizards and access SQL side not VB. I know theres always one to break the rule however I'm working on the theory some validation is better than none. Now my problem is since I've entered this input mask in my table and my members form I would like to set the curser on the first letter each time someone clicks on that field so that they dont start entering data a couple of letters 'out of sink'!

 

[Kassy]

Input mask in Parameter Query

How can I force the parameter query box to show the same input mask as the expected result e.g. AA00\ 0AA so that the user will type in the correct format to search for. I've already entered the format in the underlying table, Query etc.

 

[Delboy_64]

With the help of a bit of code from David Crake in another post . I managed to validate on the after update of the control for the sitepostcode. It may not be prity but it worked. Thanks David.

'Valid Formats (A = Alpha, N = Nueric)
'Format Example
'AN NAA M1 1AA
'AAN NAA CR2 6XH
'ANA NAA W1A 1HQ
'ANN NAA M60 1NW
'AANA NAA EC1A 1BB
'AANN NAA DN55 1PT
Private Sub SitePostCode_AfterUpdate()
'Changes the postcode to upper case to make checking using like easier
Me.SitePostCode = UCase(SitePostCode)
'AN NAA Checks eg "M1 1AA"
If Me.SitePostCode Like "[A-PR-UWY-Z]# #[A-BD-HJMNP-UW-Z][A-BD-HJMNP-UW-Z]" Then
Exit Sub
'AAN NAA Checks eg "CR2 6XH"
ElseIf Me.SitePostCode Like "[A-PR-UWY-Z][A-HK-Y]# #[A-BD-HJMNP-UW-Z][A-BD-HJMNP-UW-Z]" Then
Exit Sub
'ANA NAA Check eg "W1A 1HQ"
ElseIf Me.SitePostCode Like "[A-PR-UWY-Z]#[A-HJ-KS-UW] #[A-BD-HJMNP-UW-Z][A-BD-HJMNP-UW-Z]" Then
Exit Sub
'ANN NAA Checks eg "M60 1NW"
ElseIf Me.SitePostCode Like "[A-PR-UWY-Z]## #[A-BD-HJMNP-UW-Z][A-BD-HJMNP-UW-Z]" Then
Exit Sub
'AANA NAA Checks eg "EC1A 1BB"
ElseIf Me.SitePostCode Like "[A-PR-UWY-Z][A-HK-Y]#[A-BEHM-NPRV-Y] #[A-BD-HJMNP-UW-Z][A-BD-HJMNP-UW-Z]" Then
Exit Sub
'AANN NAA Checks eg "DN55 1PT"
ElseIf Me.SitePostCode Like "[A-PR-UWY-Z][A-HK-Y]## #[A-BD-HJMNP-UW-Z][A-BD-HJMNP-UW-Z]" Then
Exit Sub
Else: MsgBox "Please Enter A Corect Post Code", vbCritical
DoCmd.GoToControl "SitePostCode"