Problem with If statement

[Hilary Ostrov]

I have been using code to alert user to changes that need to be made
to a record under certain conditions in Form Current.

CL_Type can be either Adult or Child. The following *was* working
(along with other events and If statements in Form Current):

'unbound txtbox to display current age on form
Me.txtAge = "Age " & DateDiff("yyyy", [CL_DOB], Date) -
IIf(Format([CL_DOB], "mmdd") > Format(Date, "mmdd"), 1, 0)

If Me.txtAge > 18 And Me.CL_ARCode Like "C*" Then
Me.CL_ARCode.BackColor = "255"
MsgBox "Please change 'C' in AR Code to 'A' and make sure 'Client
Type' is 'Adult'", vbOKOnly
Else: Me.CL_ARCode.BackColor = "16777215"
End If

There was one Child record previously, and above worked as expected.
But, now that I've appended all the "Child" records, the MsgBox pops
up and BackColor changes *regardless* of Child's txtAge. Adult
records are still fine and even Child records appear as expected after
hitting OK.

So I've tried various modifications - with no success - including
(most recently):

Dim stClientType As String
stClientType = "Child"

If Me.txtAge.Value > 18 And Me.CL_Type = stClientType Then
Me.CL_ARCode.BackColor = "255"
MsgBox "Please change 'C' in AR Code to 'A' and make sure 'Client
Type' is 'Adult'", vbOKOnly
Else: Me.CL_ARCode.BackColor = "16777215"
End If

<sigh> If someone would be good enough to show me the error of my
ways, I'd be eternally grateful

Thanks.
hro

 

[ruralguy via AccessMonster.com]

If I am reading your code correctly you are setting the txtAge control to a
string!
Me.txtAge = "Age " & DateDiff("yyyy", [CL_DOB], Date) -
IIf(Format([CL_DOB], "mmdd") > Format(Date, "mmdd"), 1, 0)
...so noe Me.txtAge is equal to the string "Age " & a number.

Then you are testing the control against a number!
If Me.txtAge > 18

...not very likely to work the way you want it to!

I have been using code to alert user to changes that need to be made
to a record under certain conditions in Form Current.

CL_Type can be either Adult or Child. The following *was* working
(along with other events and If statements in Form Current):

'unbound txtbox to display current age on form
Me.txtAge = "Age " & DateDiff("yyyy", [CL_DOB], Date) -
IIf(Format([CL_DOB], "mmdd") > Format(Date, "mmdd"), 1, 0)

If Me.txtAge > 18 And Me.CL_ARCode Like "C*" Then
Me.CL_ARCode.BackColor = "255"
MsgBox "Please change 'C' in AR Code to 'A' and make sure 'Client
Type' is 'Adult'", vbOKOnly
Else: Me.CL_ARCode.BackColor = "16777215"
End If

There was one Child record previously, and above worked as expected.
But, now that I've appended all the "Child" records, the MsgBox pops
up and BackColor changes *regardless* of Child's txtAge. Adult
records are still fine and even Child records appear as expected after
hitting OK.

So I've tried various modifications - with no success - including
(most recently):

Dim stClientType As String
stClientType = "Child"

If Me.txtAge.Value > 18 And Me.CL_Type = stClientType Then
Me.CL_ARCode.BackColor = "255"
MsgBox "Please change 'C' in AR Code to 'A' and make sure 'Client
Type' is 'Adult'", vbOKOnly
Else: Me.CL_ARCode.BackColor = "16777215"
End If

<sigh> If someone would be good enough to show me the error of my
ways, I'd be eternally grateful

Thanks.
hro

 

[Linq Adams via AccessMonster.com]

Allan is correct, of course. The next question that comes to mind, after you
get this fixed, is why are you using a messagebox to instruct the user to
change data, based on the age, rather than simply changing the data thru code?

 

[Hilary Ostrov]

Thank you, Allan ... how could I have been so dumb as to not spot
that?! Took out the ' "Age " &', dropped "Age " into a label and it
works fine now, of course!

Thanks, again
Hilary

If I am reading your code correctly you are setting the txtAge control to a
string!
Me.txtAge = "Age " & DateDiff("yyyy", [CL_DOB], Date) -
IIf(Format([CL_DOB], "mmdd") > Format(Date, "mmdd"), 1, 0)
..so noe Me.txtAge is equal to the string "Age " & a number.

Then you are testing the control against a number!
If Me.txtAge > 18

..not very likely to work the way you want it to!

Hilary Ostrov wrote:

[...]

hro

 

[ruralguy via AccessMonster.com]

Hi Hilary,
Glad we could be of assistance here.

Thank you, Allan ... how could I have been so dumb as to not spot
that?! Took out the ' "Age " &', dropped "Age " into a label and it
works fine now, of course!

Thanks, again
Hilary

If I am reading your code correctly you are setting the txtAge control to a
string!

[quoted text clipped - 6 lines]

..not very likely to work the way you want it to!

[...]

hro

 

[Hilary Ostrov]

Allan is correct, of course. The next question that comes to mind, after you
get this fixed, is why are you using a messagebox to instruct the user to
change data, based on the age, rather than simply changing the data thru code?

Good question! And I actually did consider doing this ... however,
there are other fields which the user will have to manually change
because there are options that must be selected for each Adult record
which are set to "N/A" for those < 19.

Not to mention that from their existing data [some of which may not be
accurate, and all of which will require their review], it appears that
there are exceptions and at this point it is not certain that the
practice of using C and A prepended to the AR_Code will continue.

Consequently, my thinking was that the messagebox will serve as a
multi-purpose reminder to the user.

But thanks for the suggestion

hro