String Validation in VBA for Access

[tori712]

Hello. I am trying to set up a before update event procedure to verify a
Canadian postal code with a certain format (A1A 1A1). I am a novice with Access and VBA, so bear with me.

Below is my code (EmployeePostalCode is the field name).
----------------------------------------------------------------
Private Sub EmployeePostalCode_BeforeUpdate(Cancel As Integer)

'Check format of EmployeePostalCode
Dim EmployeePostalCode As String

' Check format
If (UCase$(EmployeePostalCode) Like "[A-Z]#[A-Z] #[A-Z]#") Then _
Exit Sub

' If validation fails, display error message
EmployeePostalCode.SetFocus
MsgBox "Postal Code should have format A#A #A#."



End Sub
---------------------------------------------------------

It seems to work sparingly...What am I doing wrong here? I would appreciate any help I can get =0)

 

[tori712]

I may have an End If in there at the end, but basically, that is the format.

 

[jdraw]

I think that these characters are not used in Canadian Postal codes (at least they weren't a few years ago.

"F", "D", "I", "O", "Q", "U"

 

[tori712]

Thank you, jdraw.

This is not real-world; it's just for a class (and it's due tonight!). I am not concerned about verifying that the code is actually valid, just that it is in the A1A1A1 format. I am not even worried about the space at this point.

It is the only problem that is holding me up.

I cannot find anything that works.

I have taken programming courses before and have done fine, but I don't understand WHY this will not work for me.