Solved

Access VBA: weird result of comparison of 2 variants

Posted on 2014-07-21
6
316 Views
Last Modified: 2014-07-22
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
0
Comment
Question by:bthouin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 150 total points
ID: 40208390
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
 
LVL 84
ID: 40208599
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 150 total points
ID: 40208641
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:bthouin
ID: 40209391
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
 
LVL 1

Author Comment

by:bthouin
ID: 40209402
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
 
LVL 57
ID: 40209418
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question