Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Canadian Postal codes (A2K SP-3)

    I currently have a Postal Code field in a database that is a 10 character Text field with an Input Mask of 0000-9999;0;_. I need to modify the mask to accept Canadian postal codes. I know the Canadian postal code contains both alpha character and numbers but I don't know the exact format.

    Any assistance would be appreciated.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Canadian Postal codes (A2K SP-3)

    Try this:<pre>>L0L 0L0</pre>

    Those 0s are zeros, not capital Os.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canadian Postal codes (A2K SP-3)

    From your reply, I now understand the Canadian format but do not understand how to have an input mask for both the US zip plus 4 and the Canadian 6 character format.

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Canadian Postal codes (A2K SP-3)

    Access does not support multiple input masks, unfortunately. One workaround is to have separate fields for USA zip codes and Canadian postal codes. Otherwise, you'll have to check for valid input on a form, for example in the Before Update event of the text box or of the form. Something like (incomplete):

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case Me.cboCountry
    Case 1 ' USA
    ' insert code to check for valid USA zip+4 here
    If ... Then
    MsgBox "Not a valid USA ZIP+4", vbInformation
    Me.txtZipCode.SetFocus
    Cancel = True
    End If
    Case 2 ' Canada
    ' insert code to check for valid Canadian postal code here
    If ... Then
    MsgBox "Not a valid Canadian postal code", vbInformation
    Me.txtZipCode.SetFocus
    Cancel = True
    End If
    End Select
    End Sub

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canadian Postal codes (A2K SP-3)

    Thanks for pointing me in the right direction. I probably have a dozen of so objets that use the postal code so using a seperate Canadia postal code is certainly doable.

    I will have to give some thought to writing the validation code. I am very new to VBA. I surpose you would have to test for the various text lengths and whether the character were numbers in USA format and for the Canadian whether the appropriate character was a letter or number.

    I will give it a try.

    If anyone has an examble, it would be greatly appreciated. It would seem that having US and Canadian postage codes in North America would be a rather common occurance.

    Once again thanks for pointing me in the right direction.

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Canadian Postal codes (A2K SP-3)

    You can also change the Input Mask in code:

    Private Sub AdaptMask()
    Select Case cboCountry
    Case 1 ' USA
    Me.txtZip.InputMask = "00000 0000;0;_"
    Case 2 ' Canada
    Me.txtZip.InputMask = ">L0L 0L0;0;_"
    Case Else
    Me.txtZip.InputMask = ""
    End Select
    End Sub

    Private Sub Form_Current()
    AdaptMask
    End Sub

    Private Sub cboCountry_AfterUpdate()
    AdaptMask
    End Sub

    The On Current event of the form and the After Update event of the combo box used to select the country set the input mask by calling a common procedure.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canadian Postal codes (A2K SP-3)

    FrmCustomerEntry has
    1. A text box whose Control Source in Country and whose name is txtCountry
    2. A text box whose Control is PostalCode and whose name is txtZip

    The database has a Country text field whose default value was set the USA. I have not yet added
    a combo box to select the country.

    I adapted your code as follows:

    Private Sub AdaptMask()
    Select Case txtCountry
    Case 1 ' USA
    Me.txtZip.InputMask = "00000 0000;0;_"
    Case 2 ' Canada
    Me.txtZip.InputMask = ">L0L 0L0;0;_"
    Case Else
    Me.txtZip.InputMask = ""
    End Select
    End Sub

    Private Sub Form_Current()
    AdaptMask
    End Sub

    Private Sub txtCountry_AfterUpdate()
    AdaptMask
    End Sub

    When entering the Zip code/Postal code, the input mask does not restrict the character entry. For
    example when the Country field contains Canada, a letter can be entered in any position in either
    upper or lower case and the field is not limited to 6 characters. In addition, the input make does
    not appear when the field has focus.

    Any suggestions?

    Tom

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Canadian Postal codes (A2K SP-3)

    If you have a text box for the country, it probably contains the country name, not some numeric code. So instead of Case 1, use Case "USA", and instead of Case 2, use Case "Canada". You can add other Case options if you have more countries for which you know the zip code/postal code format. All countries not explicitly listed will be handled in Case Else, i.e. no input mask.

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canadian Postal codes (A2K SP-3)

    Many thanks for your assistance. Now working great.

    tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •