If...Like...Then

[Stephanie]

Hi. I'm having a problem in my function getting the correct msg box to
appear. One of the discussion group members suggested using:
If Like2(strZip, "@#@#@#") Then

No idea what Like2 is. I'd appreciate any insight as to why my code isn't
functioning correctly. Thanks so much!

Private Sub PostalCode1_LostFocus()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
strZip = Me.PostalCode1.Value
CheckMailCodeFormat
End If

End Sub

Function CheckMailCodeFormat()

Select Case Len(strZip)
Case 5
'5-Digit US StrZip code
If Not strZip Like "#####" Then
MsgBox "'" & strZip & "' is not a valid 5-digit US Zip code."
Me.PostalCode1.Value = Null
End If
Case 6
'Canadian postal code (without space)
'If Like2(strZip, "@#@#@#") Then
If strZip Like "@#@#@#" Then
Me.PostalCode1.Value = UCase(Format(strZip, "@@@ @@@"))
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If
Case 7

 

[Klatuu]

For US - Expecting 5 digits
Not strZip Like("#####")
For Canada - Expecting Letter, Number, Letter, Space, Number, Letter, Number
Not strZip LIke(""?#? #?#"")

Never heard of Like2

 

[Stephanie]

I apologize for my delayed response and appreciate your efforts in addressing
my question. I believe that I am having difficulty bringing a value into my
function.

Private Sub PostalCode1_LostFocus()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
strZip = Me.PostalCode1.Value
CheckMailCodeFormat
End If

End Sub

Here, Me.PostalCode1.Value is for example, 84157-1234 and strZip is the
same. However, when I check strZip in my function, I get strZip=Empty. I'll
post the entire function...

Function CheckMailCodeFormat()

Select Case Len(strZip)
Case 5
'5-Digit US StrZip code
If Not strZip Like "#####" Then
MsgBox "'" & strZip & "' is not a valid 5-digit US Zip code."
Me.PostalCode1.Value = Null
End If
Case 6
'Canadian postal code (without space)
'If strZip Like "@#@#@#" Then
'If Not strZip Like ""?#? #?#"" Then

If strZip Like "[a-z]#[a-z]#[a-z]#" Then
Me.PostalCode1.Value = UCase(Format(strZip, "@@@ @@@"))
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If
Case 7
'Canadian postal code (with space)
If strZip Like "@#@ #@#" Then
Me.PostalCode1.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If
Case 9
'9-Digit US StrZip code (without hyphen)
If strZip Like "#########" Then
Me.PostalCode1.Value = Format(strZip, "@@@@@-@@@@")
Else
MsgBox "'" & strZip & "' is not a valid 9-digit US Zip code."
Me.PostalCode1.Value = Null
End If
Case 10
'9-Digit US StrZip code (with hyphen)
If Not strZip Like "#####-####" Then
MsgBox "'" & strZip & "' is not a valid 9-digit US Zip code."
Me.PostalCode1.Value = Null
End If
Case Else
'Non-postal code
MsgBox "'" & strZip & "' is not a valid 5- or 9-digit US Zip code or
Canadian postal code."
Me.PostalCode1.Value = Null
End Select
End Function