If statement not providing correct result

[T2T2]

I have got myself into trouble, but don't understandd what could be the problem.

I have an if statement that does not follow the condition test?

In my recordset I have a field that contains either the word "Withdrawn" or is null.
I assigned this to a string before applying the if statement.

The code is :

If rs.Fields("withdrawnprize").Value <> "" Then
wstatus = rs.Fields("withdrawnprize").Value & " "
MsgBox " rs withdrawnprize is not <> null "
Else
wstatus = ""
End If

If wstatus = "Withdrawn" Then
MsgBox " withdrawnprize record found **** "
chkExpired.Value = 1
Else
chkExpired.Value = 0
MsgBox " what is going on here wstatus " & wstatus
End If

The msgboxs show the recordset column and the string to contain the word Withdrawn , the If statement always branches to the else code?

Can anyone see how I have miss coded this IF logic?

[Ahmed Saber]

NULL and EMPTY are not same. Try the following code

If isnull(rs.Fields("withdrawnprize").value) Then
msgbox "Value is null
else
if rs.Fields("withdrawnprize").value = "" then
msgbox "Value is empty"
else
' value found
' other code
endif
endif

[M Owen]

NULL and EMPTY are not same ...

Too true Ahmed. You have sinned to the other side tho ... A way to handle both issues at the same time is:

If rs.Fields("withdrawnprize").value & "" <> "" Then
...

Remember IsNull will fail on an empty string ...

[Cimperiali]

Moreover, null propagate:
if X <>"a" then

if x value is null, you never enter the if, as null propagate and
expression is never evaluate as different

If x="a" then
if x is null, you always enter the if for the same reason

[T2T2]

Thanks to all the responses.

I have used ahmed's approach.

The field that I am testing against was introduced into my SQL server database after I started testing, therefore I had a combination of values (null, spaces, and the word withdrawn ).

I believe when I go into production I will only ever have nulls or "withdrawn"

Interestingly, when my if statement tests for a space the condition failed , but when I testsed for 10 spaces ( the size of the column ) it worked???

Cimperiali, I don't understand the null propagate concept?
As a guess, are you saying that the null value is interpretted as being equal to the test value. Therefore the result will always be as if the test value and the condition value are the same??
Hard to explain, hard to understand?

[Cimperiali]

Rule is:
"An expression containing null is always evaluated as null"
Thus:
if Null<>1
becomes
if Null<>Null
and

If Null=1
becomes
if Null=Null

[T2T2]

Cimperiali,

so if a variable/object can contain null I need to do the IsNull test to avoid comparing it against a value, which will be interpretted as null value.
(IF condition will always substitute a null test against the variable/object if it contains a null value?)


IF variable <> 1 .........
IF object = 1 ............

I think I understand?

Thanks

[Cimperiali]

Yes, when dealing with Null, you should use IsNull

[kimmckenzie]

Originally posted by T2T

The msgboxs show the recordset column and the string to contain the word Withdrawn , the If statement always branches to the else code?

Can anyone see how I have miss coded this IF logic?

Back to your original problem, if the message box shows that the field has "withdrawn" in it then it's possible that there is a leading or trailing space on it or that the case doesn't match.

You can test this way

If trim(UCASE(wstatus)) = "WITHDRAWN" then
do what you're going to do
End if

[Twodogs]

Best bet would be to clean your data up first.

Update them to 'WITHDRAWN' like this...

UPDATE <tablename> SET WStatus = 'WITHDRAWN' WHERE WStatus like '%withdr%'
OR ISNULL(WStatus)
OR WStatus like '% %'

Of course, before running this, make sure you back up your database....and check that the last OR will only update the records that you want (in other words, make sure there are no other records that contain at least one space)

[Vi2]

To Cimperiali: You are incorrect in conclusion

Rule is:
"An expression containing null is always evaluated as null"
Thus:
if Null<>1 becomes if Null<>Null
and
If Null=1 becomes if Null=Null

Both "Null<>1" and "Null=1" will be simply "Null" and therefore be converted into boolean "False".

Because

MSDN
The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string.

Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False.

HTH

[Cimperiali]

Originally posted by Vi2
To Cimperiali: You are incorrect in conclusion

Both "Null<>1" and "Null=1" will be simply "Null" and therefore be converted into boolean "False".

Yes, now I see.
Thanks for pointing it out: is nice for me to learn something more every day.

Cesare

[T2T2]

It makes sense what Vi2 is saying.

VB treats a null as no data to compare situation and returns a false condition.

Kimmckenzie, I started using the Trim function recently, your example would help in situations where different users enter data inconsistantly, very useful.

Twodogs, I assign the withdraw value in my code, so it is always consistant. The assignment is triggered by a tick box on the form.
I will keep a copy of the sql it to looks useful.


Thanks, I have the result and explanation I was looking for.