Compare OldValue and value

  • Thread starter Thread starter Stu
  • Start date Start date
S

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?
 
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
 
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.
 
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.
 
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
....
 
Back
Top