If...Like...Then

G

Guest

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
 
M

Marshall Barton

Stephanie said:
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
Click to expand...


Like2 looks like a user defined function, so you'll probably
the autor to find out what it does.

I don't know what a Canadian post code looks like, but a
wild quess is that alternates letter - number. If that's
right, you can use Like "[a-z]#[a-z]#[a-z]#"
 
G

Guest

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
 
G

Guest

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
 
G

Guest

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.

I posted my sub and function under Klatuu's 10/13 response- I'm guessing
it's poor etiquette to post it twice. But I would still appreciate your
help. Thanks.

Marshall Barton said:
Stephanie said:
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
Click to expand...


Like2 looks like a user defined function, so you'll probably
the autor to find out what it does.

I don't know what a Canadian post code looks like, but a
wild quess is that alternates letter - number. If that's
right, you can use Like "[a-z]#[a-z]#[a-z]#"
Click to expand...
 
M

Marshall Barton

The Like operator does not understand @. Change that to
[a-z]. The @s used in the Format function are fine, so
don't change those.

I recommend that you use the AfterUpdate event instead of
the LostFocus event. There's no point in calling the
procedure unless the value has been changed.

I don't understand why you are using the strZip variable and
maybe it's at least part of the problem. Generally you
should pass the value to the procedure as an argument.
Also, since you do not intend to return a value from the
procedure, it should be a Sub instead of a Function. E.g.

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
Sub CheckMailCodeFormat(strZip As Variant)
End If
End Sub CheckMailCodeFormat Me.PostalCode1.Value

Sub CheckMailCodeFormat(strZip)
. . .
--
Marsh
MVP [MS Access]


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.

I posted my sub and function under Klatuu's 10/13 response- I'm guessing
it's poor etiquette to post it twice. But I would still appreciate your
help. Thanks.

Marshall Barton said:
Like2 looks like a user defined function, so you'll probably
the autor to find out what it does.

I don't know what a Canadian post code looks like, but a
wild quess is that alternates letter - number. If that's
right, you can use Like "[a-z]#[a-z]#[a-z]#"
Click to expand...
Click to expand...
 
G

Guest

Thanks for the tips. All I'm trying to do is put in place logic that will
make sure that I have appropriate US and Canadian zip codes so that I can
make address labels. I'd take any suggestions to streamline as my coding is
crappy.

I am trying to fix my first sub with your suggestions but I can't quite
figure out the order of where CheckMailCodeFormat Me.PostalCode1.Value is
suppose to go since I'm not quite sure what I'm doing:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
'strZip = Me.PostalCode1.Value
Sub CheckMailCodeFormat(strZip As Variant)
End If
CheckMailCodeFormat Me.PostalCode1.Value
End Sub

I've obviously done something wrong as my form won't open and I'm getting a
run-time error: 40036 'Method' item of 'object' forms failed. When I click
debug, I'm at the IsLoaded function where I'm told that the form that I'm
working on has IsLoaded = False.

It doesn't seem like it should be that difficult to make sure my form zip
code formatting is correct. I'd appreciate your guidance.

Marshall Barton said:
The Like operator does not understand @. Change that to
[a-z]. The @s used in the Format function are fine, so
don't change those.

I recommend that you use the AfterUpdate event instead of
the LostFocus event. There's no point in calling the
procedure unless the value has been changed.

I don't understand why you are using the strZip variable and
maybe it's at least part of the problem. Generally you
should pass the value to the procedure as an argument.
Also, since you do not intend to return a value from the
procedure, it should be a Sub instead of a Function. E.g.

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
Sub CheckMailCodeFormat(strZip As Variant)
End If
End Sub CheckMailCodeFormat Me.PostalCode1.Value

Sub CheckMailCodeFormat(strZip)
. . .
--
Marsh
MVP [MS Access]


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.

I posted my sub and function under Klatuu's 10/13 response- I'm guessing
it's poor etiquette to post it twice. But I would still appreciate your
help. Thanks.

Stephanie wrote:
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
Click to expand...
Marshall Barton said:
Like2 looks like a user defined function, so you'll probably
the autor to find out what it does.

I don't know what a Canadian post code looks like, but a
wild quess is that alternates letter - number. If that's
right, you can use Like "[a-z]#[a-z]#[a-z]#"
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

I'm sorry Stephanie, somehow my pasting got all garbled.
Here's what it was supposed to be:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
CheckMailCodeFormat Me.PostalCode1.Value
End If
End Sub

I also forgot to mention to make sure that the AfterUpdate
event **property** is set to [Event Procedure]. The old
OnLostFocus property should have nothing in it.

Make sure you modify the CheckMailCodeFormat procedure to
atay in sync with its new usage.
--
Marsh
MVP [MS Access]

Thanks for the tips. All I'm trying to do is put in place logic that will
make sure that I have appropriate US and Canadian zip codes so that I can
make address labels. I'd take any suggestions to streamline as my coding is
crappy.

I am trying to fix my first sub with your suggestions but I can't quite
figure out the order of where CheckMailCodeFormat Me.PostalCode1.Value is
suppose to go since I'm not quite sure what I'm doing:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
'strZip = Me.PostalCode1.Value
Sub CheckMailCodeFormat(strZip As Variant)
End If
CheckMailCodeFormat Me.PostalCode1.Value
End Sub

I've obviously done something wrong as my form won't open and I'm getting a
run-time error: 40036 'Method' item of 'object' forms failed. When I click
debug, I'm at the IsLoaded function where I'm told that the form that I'm
working on has IsLoaded = False.

It doesn't seem like it should be that difficult to make sure my form zip
code formatting is correct. I'd appreciate your guidance.

Marshall Barton said:
The Like operator does not understand @. Change that to
[a-z]. The @s used in the Format function are fine, so
don't change those.

I recommend that you use the AfterUpdate event instead of
the LostFocus event. There's no point in calling the
procedure unless the value has been changed.

I don't understand why you are using the strZip variable and
maybe it's at least part of the problem. Generally you
should pass the value to the procedure as an argument.
Also, since you do not intend to return a value from the
procedure, it should be a Sub instead of a Function. E.g.

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
Sub CheckMailCodeFormat(strZip As Variant)
End If
End Sub CheckMailCodeFormat Me.PostalCode1.Value

Sub CheckMailCodeFormat(strZip)
. . .
--
Marsh
MVP [MS Access]


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.

I posted my sub and function under Klatuu's 10/13 response- I'm guessing
it's poor etiquette to post it twice. But I would still appreciate your
help. Thanks.


Stephanie wrote:
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


:
Like2 looks like a user defined function, so you'll probably
the autor to find out what it does.

I don't know what a Canadian post code looks like, but a
wild quess is that alternates letter - number. If that's
right, you can use Like "[a-z]#[a-z]#[a-z]#"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Mashall.
I've changed the sub as you suggested, but I am receiving an error at the
CheckMailCodeFormat of my PostalCode1_AfterUpdate() sub:

Compile error: wrong number of arguments or invalid property assignment.

So what I have is:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
CheckMailCodeFormat Me.PostalCode1.Value
End If
End Sub

Sub CheckMailCodeFormat()

Select Case Len(strZip)
Case 5.....

I'd appreciate your suggestions.
Thanks,
Stephanie

Marshall Barton said:
I'm sorry Stephanie, somehow my pasting got all garbled.
Here's what it was supposed to be:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
CheckMailCodeFormat Me.PostalCode1.Value
End If
End Sub

I also forgot to mention to make sure that the AfterUpdate
event **property** is set to [Event Procedure]. The old
OnLostFocus property should have nothing in it.

Make sure you modify the CheckMailCodeFormat procedure to
atay in sync with its new usage.
--
Marsh
MVP [MS Access]

Thanks for the tips. All I'm trying to do is put in place logic that will
make sure that I have appropriate US and Canadian zip codes so that I can
make address labels. I'd take any suggestions to streamline as my coding is
crappy.

I am trying to fix my first sub with your suggestions but I can't quite
figure out the order of where CheckMailCodeFormat Me.PostalCode1.Value is
suppose to go since I'm not quite sure what I'm doing:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
'strZip = Me.PostalCode1.Value
Sub CheckMailCodeFormat(strZip As Variant)
End If
CheckMailCodeFormat Me.PostalCode1.Value
End Sub

I've obviously done something wrong as my form won't open and I'm getting a
run-time error: 40036 'Method' item of 'object' forms failed. When I click
debug, I'm at the IsLoaded function where I'm told that the form that I'm
working on has IsLoaded = False.

It doesn't seem like it should be that difficult to make sure my form zip
code formatting is correct. I'd appreciate your guidance.

Marshall Barton said:
The Like operator does not understand @. Change that to
[a-z]. The @s used in the Format function are fine, so
don't change those.

I recommend that you use the AfterUpdate event instead of
the LostFocus event. There's no point in calling the
procedure unless the value has been changed.

I don't understand why you are using the strZip variable and
maybe it's at least part of the problem. Generally you
should pass the value to the procedure as an argument.
Also, since you do not intend to return a value from the
procedure, it should be a Sub instead of a Function. E.g.

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
Sub CheckMailCodeFormat(strZip As Variant)
End If
End Sub CheckMailCodeFormat Me.PostalCode1.Value

Sub CheckMailCodeFormat(strZip)
. . .
--
Marsh
MVP [MS Access]



Stephanie wrote:
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.

I posted my sub and function under Klatuu's 10/13 response- I'm guessing
it's poor etiquette to post it twice. But I would still appreciate your
help. Thanks.


Stephanie wrote:
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


:
Like2 looks like a user defined function, so you'll probably
the autor to find out what it does.

I don't know what a Canadian post code looks like, but a
wild quess is that alternates letter - number. If that's
right, you can use Like "[a-z]#[a-z]#[a-z]#"
Click to expand...
Click to expand...
Click to expand...
 
R

Rob Oldfield

It's to do with the number of arguments provided for/expected by the
CheckMailCodeFormat sub. When you're calling it from
PostalCode1_AfterUpdate then you're providing an argument -
Me.PostalCode1.Value. But in the actual sub itself you're not saying that
it will actually have one.

Try changing

Sub CheckMailCodeFormat()

to

Sub CheckMailCodeFormat(strZip)

....that way you have a match between the number of arguments you're feeding
the sub, and the number it is expecting. The idea is that when you call the
sub you give it a value: Me.PostalCode1.Value, 23, "23" or whatever - it
just gets turned into a value. When the sub kicks off it justs sets the
variable in the function itself - strZip in this case - to be whatever value
has been provided.




Stephanie said:
Thanks Mashall.
I've changed the sub as you suggested, but I am receiving an error at the
CheckMailCodeFormat of my PostalCode1_AfterUpdate() sub:

Compile error: wrong number of arguments or invalid property assignment.

So what I have is:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
CheckMailCodeFormat Me.PostalCode1.Value
End If
End Sub

Sub CheckMailCodeFormat()

Select Case Len(strZip)
Case 5.....

I'd appreciate your suggestions.
Thanks,
Stephanie

Marshall Barton said:
I'm sorry Stephanie, somehow my pasting got all garbled.
Here's what it was supposed to be:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
CheckMailCodeFormat Me.PostalCode1.Value
End If
End Sub

I also forgot to mention to make sure that the AfterUpdate
event **property** is set to [Event Procedure]. The old
OnLostFocus property should have nothing in it.

Make sure you modify the CheckMailCodeFormat procedure to
atay in sync with its new usage.
--
Marsh
MVP [MS Access]

Thanks for the tips. All I'm trying to do is put in place logic that will
make sure that I have appropriate US and Canadian zip codes so that I can
make address labels. I'd take any suggestions to streamline as my coding is
crappy.

I am trying to fix my first sub with your suggestions but I can't quite
figure out the order of where CheckMailCodeFormat Me.PostalCode1.Value is
suppose to go since I'm not quite sure what I'm doing:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
'strZip = Me.PostalCode1.Value
Sub CheckMailCodeFormat(strZip As Variant)
End If
CheckMailCodeFormat Me.PostalCode1.Value
End Sub

I've obviously done something wrong as my form won't open and I'm getting a
run-time error: 40036 'Method' item of 'object' forms failed. When I click
debug, I'm at the IsLoaded function where I'm told that the form that I'm
working on has IsLoaded = False.

It doesn't seem like it should be that difficult to make sure my form zip
code formatting is correct. I'd appreciate your guidance.

:

The Like operator does not understand @. Change that to
[a-z]. The @s used in the Format function are fine, so
don't change those.

I recommend that you use the AfterUpdate event instead of
the LostFocus event. There's no point in calling the
procedure unless the value has been changed.

I don't understand why you are using the strZip variable and
maybe it's at least part of the problem. Generally you
should pass the value to the procedure as an argument.
Also, since you do not intend to return a value from the
procedure, it should be a Sub instead of a Function. E.g.

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
Sub CheckMailCodeFormat(strZip As Variant)
End If
End Sub CheckMailCodeFormat Me.PostalCode1.Value

Sub CheckMailCodeFormat(strZip)
. . .
--
Marsh
MVP [MS Access]



Stephanie wrote:
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.

I posted my sub and function under Klatuu's 10/13 response- I'm guessing
it's poor etiquette to post it twice. But I would still appreciate your
help. Thanks.


Stephanie wrote:
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


:
Like2 looks like a user defined function, so you'll probably
the autor to find out what it does.

I don't know what a Canadian post code looks like, but a
wild quess is that alternates letter - number. If that's
right, you can use Like "[a-z]#[a-z]#[a-z]#"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks! I wasn't sure how to pass the parameter from one sub to another. Now
that the parameter is being passed, I see that I'm having a couple of "Like"
issues- my message boxes are being triggered when they shouldn't be. I think
I'm not taking "spaces" and "-" into consideration correctly, and I'm not
triggering a msgbox if there is no PostalCode1. Here's are 3 cases that
aren't working:

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]@[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If

Case 10
'9-Digit US StrZip code (with hyphen)
If Not strZip Like "[a-z][a-z][a-z][a-z][a-z]-[a-z][a-z][a-z][a-z]" 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

I'd appreciate your suggestions. Thanks!

Rob Oldfield said:
It's to do with the number of arguments provided for/expected by the
CheckMailCodeFormat sub. When you're calling it from
PostalCode1_AfterUpdate then you're providing an argument -
Me.PostalCode1.Value. But in the actual sub itself you're not saying that
it will actually have one.

Try changing

Sub CheckMailCodeFormat()

to

Sub CheckMailCodeFormat(strZip)

....that way you have a match between the number of arguments you're feeding
the sub, and the number it is expecting. The idea is that when you call the
sub you give it a value: Me.PostalCode1.Value, 23, "23" or whatever - it
just gets turned into a value. When the sub kicks off it justs sets the
variable in the function itself - strZip in this case - to be whatever value
has been provided.




Stephanie said:
Thanks Mashall.
I've changed the sub as you suggested, but I am receiving an error at the
CheckMailCodeFormat of my PostalCode1_AfterUpdate() sub:

Compile error: wrong number of arguments or invalid property assignment.

So what I have is:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
CheckMailCodeFormat Me.PostalCode1.Value
End If
End Sub

Sub CheckMailCodeFormat()

Select Case Len(strZip)
Case 5.....

I'd appreciate your suggestions.
Thanks,
Stephanie

Marshall Barton said:
I'm sorry Stephanie, somehow my pasting got all garbled.
Here's what it was supposed to be:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
CheckMailCodeFormat Me.PostalCode1.Value
End If
End Sub

I also forgot to mention to make sure that the AfterUpdate
event **property** is set to [Event Procedure]. The old
OnLostFocus property should have nothing in it.

Make sure you modify the CheckMailCodeFormat procedure to
atay in sync with its new usage.
--
Marsh
MVP [MS Access]


Stephanie wrote:
Thanks for the tips. All I'm trying to do is put in place logic that will
make sure that I have appropriate US and Canadian zip codes so that I can
make address labels. I'd take any suggestions to streamline as my coding is
crappy.

I am trying to fix my first sub with your suggestions but I can't quite
figure out the order of where CheckMailCodeFormat Me.PostalCode1.Value is
suppose to go since I'm not quite sure what I'm doing:

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
'strZip = Me.PostalCode1.Value
Sub CheckMailCodeFormat(strZip As Variant)
End If
CheckMailCodeFormat Me.PostalCode1.Value
End Sub

I've obviously done something wrong as my form won't open and I'm getting a
run-time error: 40036 'Method' item of 'object' forms failed. When I click
debug, I'm at the IsLoaded function where I'm told that the form that I'm
working on has IsLoaded = False.

It doesn't seem like it should be that difficult to make sure my form zip
code formatting is correct. I'd appreciate your guidance.

:

The Like operator does not understand @. Change that to
[a-z]. The @s used in the Format function are fine, so
don't change those.

I recommend that you use the AfterUpdate event instead of
the LostFocus event. There's no point in calling the
procedure unless the value has been changed.

I don't understand why you are using the strZip variable and
maybe it's at least part of the problem. Generally you
should pass the value to the procedure as an argument.
Also, since you do not intend to return a value from the
procedure, it should be a Sub instead of a Function. E.g.

Private Sub PostalCode1_AfterUpdate()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
Sub CheckMailCodeFormat(strZip As Variant)
End If
End Sub CheckMailCodeFormat Me.PostalCode1.Value

Sub CheckMailCodeFormat(strZip)
. . .
--
Marsh
MVP [MS Access]



Stephanie wrote:
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.

I posted my sub and function under Klatuu's 10/13 response- I'm guessing
it's poor etiquette to post it twice. But I would still appreciate your
help. Thanks.


Stephanie wrote:
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


:
Like2 looks like a user defined function, so you'll probably
the autor to find out what it does.

I don't know what a Canadian post code looks like, but a
wild quess is that alternates letter - number. If that's
right, you can use Like "[a-z]#[a-z]#[a-z]#"
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

Stephanie said:
Thanks! I wasn't sure how to pass the parameter from one sub to another. Now
that the parameter is being passed, I see that I'm having a couple of "Like"
issues- my message boxes are being triggered when they shouldn't be. I think
I'm not taking "spaces" and "-" into consideration correctly, and I'm not
triggering a msgbox if there is no PostalCode1. Here's are 3 cases that
aren't working:

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]@[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If

Case 10
'9-Digit US StrZip code (with hyphen)
If Not strZip Like "[a-z][a-z][a-z][a-z][a-z]-[a-z][a-z][a-z][a-z]" 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
Click to expand...


Note that [a-z] matches any letter and # matches any numeric
digit:

I still don't know what a Canadian post code is supposed to
look like. If it's A1B C3D then just replace the @ with #

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]#[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else

This one is looking for all letters when you want to look
for digits:
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
 
G

Guest

Thank you. I added a comment on my code to remember: [a-z] matches any
letter and # matches any numeric digit.

Thanks to you and Rob, I'm able to step through the code almost to the end.
I'm still hung up on the last case, that essentially if PostalCode1 is null,
give a msgbox. But. I've put this on the wrong event before. I'd like this
last msgbox to trigger at save/when the form is closed/when the user moves to
the next record (not the next field). And I guessing that
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

doesn't really tell the msgbox when to trigger.

I'm running the sub as:
Private Sub PostalCode1_AfterUpdate()

Could you please help me choose the correct event? I'm thinking it should
be on the form, not the field. Suggestions for getting the last msgbox to
trigger correctly are appreciated! Thanks.

Marshall Barton said:
Stephanie said:
Thanks! I wasn't sure how to pass the parameter from one sub to another. Now
that the parameter is being passed, I see that I'm having a couple of "Like"
issues- my message boxes are being triggered when they shouldn't be. I think
I'm not taking "spaces" and "-" into consideration correctly, and I'm not
triggering a msgbox if there is no PostalCode1. Here's are 3 cases that
aren't working:

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]@[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If

Case 10
'9-Digit US StrZip code (with hyphen)
If Not strZip Like "[a-z][a-z][a-z][a-z][a-z]-[a-z][a-z][a-z][a-z]" 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
Click to expand...


Note that [a-z] matches any letter and # matches any numeric
digit:

I still don't know what a Canadian post code is supposed to
look like. If it's A1B C3D then just replace the @ with #

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]#[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else

This one is looking for all letters when you want to look
for digits:
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
Click to expand...
 
M

Marshall Barton

I son't know what you form's behavior is supposed to be, so
I can't be sure what event you want to use for this purpose.

The AfterUpdate event will run the code whenever the value
of the text box is changed, which might be an ok(?) place to
do this.

THe Case Else should be catching the situation where the
user removes whatever was there before. OTOH if you want to
check if the user failed to enter anything on a new record,
then I think you probablu use the ***form's*** BeforeUpdate
event. In the BeforeUpdate event you can set Cancel = True
to prevent the record from being saved when it doesn't pass
your tests.
--
Marsh
MVP [MS Access]

Thank you. I added a comment on my code to remember: [a-z] matches any
letter and # matches any numeric digit.

Thanks to you and Rob, I'm able to step through the code almost to the end.
I'm still hung up on the last case, that essentially if PostalCode1 is null,
give a msgbox. But. I've put this on the wrong event before. I'd like this
last msgbox to trigger at save/when the form is closed/when the user moves to
the next record (not the next field). And I guessing that
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

doesn't really tell the msgbox when to trigger.

I'm running the sub as:
Private Sub PostalCode1_AfterUpdate()

Could you please help me choose the correct event? I'm thinking it should
be on the form, not the field. Suggestions for getting the last msgbox to
trigger correctly are appreciated! Thanks.

Stephanie said:
Thanks! I wasn't sure how to pass the parameter from one sub to another. Now
that the parameter is being passed, I see that I'm having a couple of "Like"
issues- my message boxes are being triggered when they shouldn't be. I think
I'm not taking "spaces" and "-" into consideration correctly, and I'm not
triggering a msgbox if there is no PostalCode1. Here's are 3 cases that
aren't working:

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]@[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If

Case 10
'9-Digit US StrZip code (with hyphen)
If Not strZip Like "[a-z][a-z][a-z][a-z][a-z]-[a-z][a-z][a-z][a-z]" 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
Click to expand...
Click to expand...
Marshall Barton said:
Note that [a-z] matches any letter and # matches any numeric
digit:

I still don't know what a Canadian post code is supposed to
look like. If it's A1B C3D then just replace the @ with #

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]#[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else

This one is looking for all letters when you want to look
for digits:
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
Click to expand...
Click to expand...
 
G

Guest

Thanks for the tips!
Now I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.PostalCode1) = Null Then
Cancel = True
strMsg = strMsg & "Please enter a Zip Code" & vbCrLf
End If
End Sub

My msgbox isn't triggering. PostalCode1 is Null, Cancel = 0 (which I
believe should be -1 to be working), strMsg= "".
I have added this case to code that does sucessfully kick off otther
msgboxes, but don't see why this one shouldn't work when PostalCode1 is
empty. PostalCode1 is a Data Type text field.

Any suggestions? Thanks for your help.

Marshall Barton said:
I son't know what you form's behavior is supposed to be, so
I can't be sure what event you want to use for this purpose.

The AfterUpdate event will run the code whenever the value
of the text box is changed, which might be an ok(?) place to
do this.

THe Case Else should be catching the situation where the
user removes whatever was there before. OTOH if you want to
check if the user failed to enter anything on a new record,
then I think you probablu use the ***form's*** BeforeUpdate
event. In the BeforeUpdate event you can set Cancel = True
to prevent the record from being saved when it doesn't pass
your tests.
--
Marsh
MVP [MS Access]

Thank you. I added a comment on my code to remember: [a-z] matches any
letter and # matches any numeric digit.

Thanks to you and Rob, I'm able to step through the code almost to the end.
I'm still hung up on the last case, that essentially if PostalCode1 is null,
give a msgbox. But. I've put this on the wrong event before. I'd like this
last msgbox to trigger at save/when the form is closed/when the user moves to
the next record (not the next field). And I guessing that
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

doesn't really tell the msgbox when to trigger.

I'm running the sub as:
Private Sub PostalCode1_AfterUpdate()

Could you please help me choose the correct event? I'm thinking it should
be on the form, not the field. Suggestions for getting the last msgbox to
trigger correctly are appreciated! Thanks.

Stephanie wrote:
Thanks! I wasn't sure how to pass the parameter from one sub to another. Now
that the parameter is being passed, I see that I'm having a couple of "Like"
issues- my message boxes are being triggered when they shouldn't be. I think
I'm not taking "spaces" and "-" into consideration correctly, and I'm not
triggering a msgbox if there is no PostalCode1. Here's are 3 cases that
aren't working:

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]@[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If

Case 10
'9-Digit US StrZip code (with hyphen)
If Not strZip Like "[a-z][a-z][a-z][a-z][a-z]-[a-z][a-z][a-z][a-z]" 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
Click to expand...
Marshall Barton said:
Note that [a-z] matches any letter and # matches any numeric
digit:

I still don't know what a Canadian post code is supposed to
look like. If it's A1B C3D then just replace the @ with #

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]#[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else

This one is looking for all letters when you want to look
for digits:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Rob Oldfield

NOTHING ever *equals* null. Even Null=Null isn't 'true' (in fact it's null)

Try

If isnull(Me.PostalCode1) Then

instead.



Stephanie said:
Thanks for the tips!
Now I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.PostalCode1) = Null Then
Cancel = True
strMsg = strMsg & "Please enter a Zip Code" & vbCrLf
End If
End Sub

My msgbox isn't triggering. PostalCode1 is Null, Cancel = 0 (which I
believe should be -1 to be working), strMsg= "".
I have added this case to code that does sucessfully kick off otther
msgboxes, but don't see why this one shouldn't work when PostalCode1 is
empty. PostalCode1 is a Data Type text field.

Any suggestions? Thanks for your help.

Marshall Barton said:
I son't know what you form's behavior is supposed to be, so
I can't be sure what event you want to use for this purpose.

The AfterUpdate event will run the code whenever the value
of the text box is changed, which might be an ok(?) place to
do this.

THe Case Else should be catching the situation where the
user removes whatever was there before. OTOH if you want to
check if the user failed to enter anything on a new record,
then I think you probablu use the ***form's*** BeforeUpdate
event. In the BeforeUpdate event you can set Cancel = True
to prevent the record from being saved when it doesn't pass
your tests.
--
Marsh
MVP [MS Access]

Thank you. I added a comment on my code to remember: [a-z] matches any
letter and # matches any numeric digit.

Thanks to you and Rob, I'm able to step through the code almost to the end.
I'm still hung up on the last case, that essentially if PostalCode1 is null,
give a msgbox. But. I've put this on the wrong event before. I'd like this
last msgbox to trigger at save/when the form is closed/when the user moves to
the next record (not the next field). And I guessing that
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

doesn't really tell the msgbox when to trigger.

I'm running the sub as:
Private Sub PostalCode1_AfterUpdate()

Could you please help me choose the correct event? I'm thinking it should
be on the form, not the field. Suggestions for getting the last msgbox to
trigger correctly are appreciated! Thanks.


Stephanie wrote:
Thanks! I wasn't sure how to pass the parameter from one sub to another. Now
that the parameter is being passed, I see that I'm having a couple of "Like"
issues- my message boxes are being triggered when they shouldn't be. I think
I'm not taking "spaces" and "-" into consideration correctly, and I'm not
triggering a msgbox if there is no PostalCode1. Here's are 3 cases that
aren't working:

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]@[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If

Case 10
'9-Digit US StrZip code (with hyphen)
If Not strZip Like "[a-z][a-z][a-z][a-z][a-z]-[a-z][a-z][a-z][a-z]" 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


:
Note that [a-z] matches any letter and # matches any numeric
digit:

I still don't know what a Canadian post code is supposed to
look like. If it's A1B C3D then just replace the @ with #

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]#[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else

This one is looking for all letters when you want to look
for digits:
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
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

Stephanie said:
Now I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.PostalCode1) = Null Then
Cancel = True
strMsg = strMsg & "Please enter a Zip Code" & vbCrLf
End If
End Sub

My msgbox isn't triggering. PostalCode1 is Null, Cancel = 0 (which I
believe should be -1 to be working), strMsg= "".
I have added this case to code that does sucessfully kick off otther
msgboxes, but don't see why this one shouldn't work when PostalCode1 is
empty. PostalCode1 is a Data Type text field.
Click to expand...


You can not use strMsg like that. It's value from one event
is not available to another event. Besides, the form's
Before update event fires when the entire record is about to
be saved, long after the data was entered.

If PostalCode1 is Null then your code will set Cancel to
True (-1), so I have to wonder how you detrmined that it was
0 (False). If you used a breakpoint, was it after the
Cancel = True statement or was it before?

I still don't know what behavior you want from the form, but
I'll take another guess that you might(?) want to either
move the entire bunch of code to the form's BeforeUpdate
procedure OR change the above to display its own MsgBox
instead of trying to tack on to something else.
 
M

Marshall Barton

Stephanie said:
If (Me.PostalCode1) = Null Then
Click to expand...


I just did a doubletake and noticed that your If is also
wrong. You can not compare anything to Null, not even
another Null.

A good way to think of Null is that it represents something
that is unknown. The question
if something = anotherthing
can be answered, but the answer to the question
if something = unknown
is unknown as is the answer to the question
if unknown = unknown

The correct way to check for Null is:

If IsNull(Me.PostalCode1) Then
 
G

Guest

Thanks for the help on my Subs and message box. They are working correctly
and triggering correctly. I appreciate the syntax lessons.

Rob Oldfield said:
NOTHING ever *equals* null. Even Null=Null isn't 'true' (in fact it's null)

Try

If isnull(Me.PostalCode1) Then

instead.



Stephanie said:
Thanks for the tips!
Now I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.PostalCode1) = Null Then
Cancel = True
strMsg = strMsg & "Please enter a Zip Code" & vbCrLf
End If
End Sub

My msgbox isn't triggering. PostalCode1 is Null, Cancel = 0 (which I
believe should be -1 to be working), strMsg= "".
I have added this case to code that does sucessfully kick off otther
msgboxes, but don't see why this one shouldn't work when PostalCode1 is
empty. PostalCode1 is a Data Type text field.

Any suggestions? Thanks for your help.

Marshall Barton said:
I son't know what you form's behavior is supposed to be, so
I can't be sure what event you want to use for this purpose.

The AfterUpdate event will run the code whenever the value
of the text box is changed, which might be an ok(?) place to
do this.

THe Case Else should be catching the situation where the
user removes whatever was there before. OTOH if you want to
check if the user failed to enter anything on a new record,
then I think you probablu use the ***form's*** BeforeUpdate
event. In the BeforeUpdate event you can set Cancel = True
to prevent the record from being saved when it doesn't pass
your tests.
--
Marsh
MVP [MS Access]


Stephanie wrote:
Thank you. I added a comment on my code to remember: [a-z] matches any
letter and # matches any numeric digit.

Thanks to you and Rob, I'm able to step through the code almost to the end.
I'm still hung up on the last case, that essentially if PostalCode1 is null,
give a msgbox. But. I've put this on the wrong event before. I'd like this
last msgbox to trigger at save/when the form is closed/when the user moves to
the next record (not the next field). And I guessing that
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

doesn't really tell the msgbox when to trigger.

I'm running the sub as:
Private Sub PostalCode1_AfterUpdate()

Could you please help me choose the correct event? I'm thinking it should
be on the form, not the field. Suggestions for getting the last msgbox to
trigger correctly are appreciated! Thanks.


Stephanie wrote:
Thanks! I wasn't sure how to pass the parameter from one sub to another. Now
that the parameter is being passed, I see that I'm having a couple of "Like"
issues- my message boxes are being triggered when they shouldn't be. I think
I'm not taking "spaces" and "-" into consideration correctly, and I'm not
triggering a msgbox if there is no PostalCode1. Here's are 3 cases that
aren't working:

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]@[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.PostalCode1.Value = Null
End If

Case 10
'9-Digit US StrZip code (with hyphen)
If Not strZip Like "[a-z][a-z][a-z][a-z][a-z]-[a-z][a-z][a-z][a-z]" 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


:
Note that [a-z] matches any letter and # matches any numeric
digit:

I still don't know what a Canadian post code is supposed to
look like. If it's A1B C3D then just replace the @ with #

Case 7
'Canadian postal code (with space)
If strZip Like "[a-z]#[a-z] [a-z]#[a-z]" Then
Me.PostalCode1.Value = UCase(strZip)
Else

This one is looking for all letters when you want to look
for digits:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for all the lessons! The Subs are working correctly, and I'm
triggering the message box correctly. Happy day! I appreciate your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using LIKE 2
Zip code code 1
Type Mismatch in dLookup 9
Problem in the data of the file in the FTP after the saving of excelsheets 10
Word export 2
Google Maps: StartingPoint & Destination, then get results. 2
Message Parse to Access Database 6
Parse Multiple Messages 7

Top