Access VBA: weird result of comparison of 2 variants

In one of my Access 2007 apps, I have a comparison between 2 cells of 2 two-dimensional variant arrays, like this:

If SMD(jSMD, 33) <> sExcelData(nRow, nCol) then ... do something

The second array cell is always populated with data at this point, otherwise this particular if would be skipped. But the first array cell can be empty, indeed the idea was to put the cell content of the second array in the cell of the first one if that one was not yet filled.

To my huge surprise, following the code in debug mode, this if return false when the first array cell is Null. I would never in my wildest dreams have thought that " Null <> 'some string content' " would return anything but true !

Is this:
- a huge VBA bug ?
- intended behavior ?

If the latter, I'd like a rational explanation.

I had to change all my similar if's to >> If Nz(SMD(jSMD, 33), "") <> sExcelData(nRow, nCol) then ...<< to get a propoer behavior.

Thanks for enlightening me on this one.
Bernard
LVL 1
bthouinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
No it isn't a bug. It's just the way that Null works.

Read EE's harfang's article Understanding Null, Empty, Blank, #N/A, ZLS, Nothing, Missing

The value Null is said to "propagate", in that (almost) every expression containing Null evaluates to Null. This can be tested in queries, but also in VBA's immediate pane; the expressions below all return Null.

    ? Null + 2
    ? "Hello " + Null
    ? 1 / Null
    ? Len(Null)
    ? Str(Null)

That being said, Null <> 'some string content' will return null not true.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If SMD is an array, then I can't quite figure out the syntax here:

SMD(jSMD, 33) <> sExcelData(nRow, nCol)

If you're pulling values from a multi-dimension array, you typically use syntax like this:

SMD(1,1)

However, your first argument seems to point to the array itself. Maybe I'm just overlooking something obvious, but I can't quite follow this ... perhaps you could enlighten me a bit.

<disclaimer>I have not yet had a full gallon of coffee, so my judgement may be impaired</disclaimer>
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Null is handled differently in different products.   Even SQL Server changed its handling of NULL at one point.

The current thinking is that because something is Null and it's value is unknown, then it is impossible to compare it to anything, even another null.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bthouinAuthor Commented:
Hi Scott,

Yes, I guess you were in need of coffee, or rather, of a magnifying glass. The first index is called jSMD, not SMD. But the font used for this site makes the letter j nearly disappear between the opening bracket and the S of SMD... :-)

Cheers
Bernard
0
bthouinAuthor Commented:
Hi Jim

Thanks for this clearer answer. However, failing to be able to compare should, IMHO, result in a runtime error/warning, and not in an arbitrary decision that the result of the comparison is false. If the if cannot operate, why does it return a valid-looking result ?

Bernard
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I guess the only way to answer that is sometimes choices need to be made.  If can only return true or false and certainly you would not want to say true, so that leaves only false.

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.