Error 2427-visible invisible

T

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.
 
S

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
 
G

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,

Tim said:
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.
 
T

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

George Nicholson said:
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,

Tim said:
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.
 
T

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.



George Nicholson said:
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,

Tim said:
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.
 
G

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


Tim said:
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.



George Nicholson said:
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,

Tim said:
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.
 

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

clear combo box value 1
Error 2427 0
How to concatenate multiple values in text box 3
visible controls 2
Using "tiles" 1
Make field Visible/Invisible 1
Error Message Coding 10
Error 2427 - Unable to debug 0

Top