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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
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.
Jim.
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>