Compare OldValue and value

[Stu]

dim ctlControl as control
1) MsgBox ctlControl.Name & " OLD: " & ctlControl.oldValue & " NEW: " &
ctlControl.Value
2) If ctlControl.Value <> ctlControl.oldValue Then

In statement 1 above ctlControl.oldValue displays an empty/null value and
ctlControl.Value displays a value of 2.25

Yet statement 2 evaluates as false, i.e., it is NOT true that
ctlControl.Value <> ctlControl.oldValue. I would expect statement 2 to
evaluate to true. Can someone explain why statement 2 evaluates to false
when "oldValue" is null and "value" is 2.25?

 

[BruceM]

The problem may be with the use of Null. Null is not equal to anything. It
may be as simple as using the Nz function to convert null to a number:

Dim ctlControl as Control
Dim dblOld as Double

dblOld = Nz(ctlControl.oldValue,0)

MsgBox ctlControl.Name & " OLD: " & dblOld & " NEW: " & ctlControl.Value

I guessed that the data type is double. If it is currency or something
else, use that when declaring the variable.

More information about nulls here:
http://allenbrowne.com/casu-12.html

 

[Stu]

Yes, most likely the problem is with the null. I'm trying to understand
exactly what that problem is. I tried
iif(isnull(ctlControl.oldValue),"-9999",ctlControl.oldValue)...I think that
works but was looking for something a bit more straight forward.

 

[BruceM]

I suggested the Nz function, which replaces Null with a value of your
choosing:

Nz(ctlControl.oldValue,-9999)

Did you read the information about Null? If not, you should. Null is
essentially "unknown". Comparing anything with "unknown" may yield
unexpected results.

 

[Stu]

Thanks BruceM, the NZ function works fine....I kinda forgot about NZ,=.

 

[Graham Mandeno]

Hi Stu

Bruce is not quite correct when he says "comparing anything with Null may
produce unexpected results". The result is very predictable - if you
compare something with Null, you get Null.

Actually, I suppose this could be unexpected if you didn't expect it!

Anyway, to satisfy If ... Then, a result must be non-zero AND not Null.
Anything else will drip through to the Else.

So another way, instead of:

If ctlControl.Value <> ctlControl.oldValue Then
....

try:

If ctlControl.Value = ctlControl.oldValue Then
' do nothing
Else
....