Friday, February 27, 2015

Canadian/US Postal Codes

Automatic Input masks

If you have a mix of Canadian and US postal codes, you might play with the following code inserted as a Country control "After Update" Event property.

Private Sub Country_AfterUpdate()
Dim strCountry As String
strCountry = Me.Country

Select Case strCountry
Case "Canada"
Me.[PostalCode].InputMask = ">L0L\ 0L0;;_"
Case "USA"
Me.[PostalCode].InputMask = "00000-9999;;_"
Case Else
'If the country is not Canada or USA no input mask will be used
Me.[PostalCode].InputMask = ""
End Select
End Sub

As a rule, if you won't be performing numeric calculations on the data, entries should be stored as text. Social Security numbers, Phone numbers and postal codes should be stored as text.

You can use alphabetic characters in an input mask. For example, one of the sample input masks is >L0L\ 0L0 used to represent a Canadian postal code.

The ">" character in the input mask converts all the characters that follow to uppercase.

The "L" character requires an alpha entry; the "0" (zero) requires a numeric entry.

A "\"character causes the following character to be displayed as a literal character rather than a mask character.

A space appears between the three character pairs.
For example, V5P 2G1 is one valid postal code that the user could enter. The mask would prevent the user from entering two sequential alphabetic characters or numbers.
Trinity University - San Antonio, Texas:
Input mask

Definition characters used to create an input mask
Some validation rules

You can manipulate postal codes in Access by changing the data type, input mask, or format of a postal code field.

Microsoft KB 207829:
ACC2000: How to Manipulate ZIP Codes in Microsoft Access.

Also see:
Postal Codes

See all Topics

No comments: