Error 2427-visible invisible

[Tim]

I have a field, [CLASS] that bound to a textbox on the form. Depending on the
value of this field, I want other fields will show up or not. The code in the
form_Open is following.

If IsNull(Me!CLASS.Value) AND Me!CLASS.Value <> "Biology*" Then
Me!lblEmptyPrompt.Visible = True
'All control on the form will be invisible
ElseIf Me!CLASS.Value Like "Biology*" then
'All control on the form will be visible
End If
When I open the form, there is an error pops up, run-time error '2427': "You
entered an expression that has no value". I don't know why the error pops up
because I already coded for the Null or <> "Biology*" cases. How can I fix it?
Thank you for your help in advance.

 

[SteveM]

You wouldn't use <> "Biology*" it should be - Not Like "Biology*"

In any case, the first line of your code is not logical.
There is no point testing that a field is Null and not equal to something
else.

Try this:
If IsNull(Me!CLASS) OR Me!CLASS Not Like "Biology*" Then
Me!lblEmptyPrompt.Visible = True
'All control on the form will be invisible
ElseIf Me!CLASS Like "Biology*" then
'All control on the form will be visible
End If

You don't have to use .Value, it is the default.
Also, be sure that the name of your control is CLASS. While that may be the
name of the field, it may not be the name of the textbox or other control. If
you want to refer to the field rather than the control, use '.' (dot) instead
of '!' (bang).

Steve

 

[George Nicholson]

1)

....I already coded for the Null or <> "Biology*" cases

Umm, look again. You coded IsNull *AND* <> Biology.
As written, your If statement will never be True, just Null or False (if
CLASS is Null, CLASS <> "Biology* will return Null, making the line not
True, so processing checks the Else...)

Changing AND to OR might work. Or you could try:

If nz(Me!CLASS,"") <> "Biology*" Then
'Do this if True (i.e., if Null or not like Biology*)
Else
'Do this if False (i.e., like Biology*)
End If

HTH,

I have a field, [CLASS] that bound to a textbox on the form. Depending on
the
value of this field, I want other fields will show up or not. The code in
the
form_Open is following.

If IsNull(Me!CLASS.Value) AND Me!CLASS.Value <> "Biology*" Then
Me!lblEmptyPrompt.Visible = True
'All control on the form will be invisible
ElseIf Me!CLASS.Value Like "Biology*" then
'All control on the form will be visible
End If
When I open the form, there is an error pops up, run-time error '2427':
"You
entered an expression that has no value". I don't know why the error pops
up
because I already coded for the Null or <> "Biology*" cases. How can I fix
it?
Thank you for your help in advance.

 

[Tim]

Thank you both of you, Goerge and Steve. I use SQL server 2005 as the backend
so I don't know if "Not Like" sentence work. Anyway, my SQL Server is down
recently so I can not test it, but what you guys say make sense to me. I will
keep you post.
tim

1)

....I already coded for the Null or <> "Biology*" cases

Umm, look again. You coded IsNull *AND* <> Biology.
As written, your If statement will never be True, just Null or False (if
CLASS is Null, CLASS <> "Biology* will return Null, making the line not
True, so processing checks the Else...)

Changing AND to OR might work. Or you could try:

If nz(Me!CLASS,"") <> "Biology*" Then
'Do this if True (i.e., if Null or not like Biology*)
Else
'Do this if False (i.e., like Biology*)
End If

HTH,

I have a field, [CLASS] that bound to a textbox on the form. Depending on
the
value of this field, I want other fields will show up or not. The code in
the
form_Open is following.

If IsNull(Me!CLASS.Value) AND Me!CLASS.Value <> "Biology*" Then
Me!lblEmptyPrompt.Visible = True
'All control on the form will be invisible
ElseIf Me!CLASS.Value Like "Biology*" then
'All control on the form will be visible
End If
When I open the form, there is an error pops up, run-time error '2427':
"You
entered an expression that has no value". I don't know why the error pops
up
because I already coded for the Null or <> "Biology*" cases. How can I fix
it?
Thank you for your help in advance.

 

[Tim]

Thank you for your codes. It does not pop up error any more. However, I don't
know why, for example, "Biology 127" is different with "Biology*". When it
evaluates this IF statement, it execute the statement inside this IF clause.
If nz(Me!CLASS,"") <> "Biology*" Then
'Do this if True (i.e., if Null or not like Biology*)

Do you know why? Thank you for you help in advance.

1)

....I already coded for the Null or <> "Biology*" cases

Umm, look again. You coded IsNull *AND* <> Biology.
As written, your If statement will never be True, just Null or False (if
CLASS is Null, CLASS <> "Biology* will return Null, making the line not
True, so processing checks the Else...)

Changing AND to OR might work. Or you could try:

If nz(Me!CLASS,"") <> "Biology*" Then
'Do this if True (i.e., if Null or not like Biology*)
Else
'Do this if False (i.e., like Biology*)
End If

HTH,

I have a field, [CLASS] that bound to a textbox on the form. Depending on
the
value of this field, I want other fields will show up or not. The code in
the
form_Open is following.

If IsNull(Me!CLASS.Value) AND Me!CLASS.Value <> "Biology*" Then
Me!lblEmptyPrompt.Visible = True
'All control on the form will be invisible
ElseIf Me!CLASS.Value Like "Biology*" then
'All control on the form will be visible
End If
When I open the form, there is an error pops up, run-time error '2427':
"You
entered an expression that has no value". I don't know why the error pops
up
because I already coded for the Null or <> "Biology*" cases. How can I fix
it?
Thank you for your help in advance.

 

[George Nicholson]

Wildcards and =, < or > do not mix. Wildcard characters will be taken
literally during comparison.
Wildcards and "Like" do mix. They are designed to be used together.
(I did not catch the above in my initial post, but Steve did. My code was
wrong in that respect)

If nz(Me!CLASS,"") <> "Biology*" Then

When using <>, CLASS would have to equal "Biology*" EXACTLY for the
statement to be False. Any other value for CLASS will result in a result of
True.

If nz(Me!CLASS,"") Not Like "Biology*" Then

would probably give you the results you expect.

--
HTH,
George

Thank you for your codes. It does not pop up error any more. However, I
don't
know why, for example, "Biology 127" is different with "Biology*". When it
evaluates this IF statement, it execute the statement inside this IF
clause.
If nz(Me!CLASS,"") <> "Biology*" Then
'Do this if True (i.e., if Null or not like Biology*)

Do you know why? Thank you for you help in advance.

1)

....I already coded for the Null or <> "Biology*" cases

Umm, look again. You coded IsNull *AND* <> Biology.
As written, your If statement will never be True, just Null or False (if
CLASS is Null, CLASS <> "Biology* will return Null, making the line not
True, so processing checks the Else...)

Changing AND to OR might work. Or you could try:

If nz(Me!CLASS,"") <> "Biology*" Then
'Do this if True (i.e., if Null or not like Biology*)
Else
'Do this if False (i.e., like Biology*)
End If

HTH,

I have a field, [CLASS] that bound to a textbox on the form. Depending
on
the
value of this field, I want other fields will show up or not. The code
in
the
form_Open is following.

If IsNull(Me!CLASS.Value) AND Me!CLASS.Value <> "Biology*" Then
Me!lblEmptyPrompt.Visible = True
'All control on the form will be invisible
ElseIf Me!CLASS.Value Like "Biology*" then
'All control on the form will be visible
End If
When I open the form, there is an error pops up, run-time error '2427':
"You
entered an expression that has no value". I don't know why the error
pops
up
because I already coded for the Null or <> "Biology*" cases. How can I
fix
it?
Thank you for your help in advance.