For what its worth, here is a function to check the format.
Code:
Public Function ValidatePC(pPC As String) As String
'validates format is correct.
'still need to add checks for invalid lettters in certain positions
Const Alpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim strPC As String
Dim SearchChar As String
Dim PCode As String
Dim isValid As Boolean
Dim TestPos As Integer
Dim x As Integer
Dim y As Integer
'LN NLL or LLN NLL or LLNN NLL
'set default return value
ValidatePC = "Invalid PC" ' PC = Postal Code
strPC = UCase(pPC)
Select Case Len(strPC)
Case 6
'LN NLL
For x = 1 To 6
TestPos = 0
SearchChar = Mid(strPC, x, 1)
Select Case x
Case 1, 5, 6
isValid = InStr(Alpha, SearchChar) > 0
If Not isValid Then
Exit For
End If
Case 2, 4
isValid = IsNumeric(SearchChar)
If Not isValid Then
Exit For
End If
Case 3
End Select
Next
Case 7
'LLN NLL
For x = 1 To 7
TestPos = 0
SearchChar = Mid(strPC, x, 1)
Select Case x
Case 1, 2, 6, 7
isValid = InStr(Alpha, SearchChar) > 0
If Not isValid Then
Exit For
End If
Case 3, 5
isValid = IsNumeric(SearchChar)
If Not isValid Then
Exit For
End If
Case 4
End Select
Next
Case 8
'LLNN NLL
For x = 1 To 8
TestPos = 0
SearchChar = Mid(strPC, x, 1)
Select Case x
Case 1, 2, 7, 8
isValid = InStr(Alpha, SearchChar) > 0
If Not isValid Then
Exit For
End If
Case 3, 4, 6
isValid = IsNumeric(SearchChar)
If Not isValid Then
Exit For
End If
Case 5
End Select
Next
Case Else
End Select
If isValid Then
ValidatePC = strPC
End If
End Function