This may be more of a rant than a question at this point, as I've been debugging for two days and feeling like I was getting almost nowhere.

I finally broke down and wrote a test function in a standalone module which would do nothing but call the functions I have written to auto-generate SQL (I have a need to produce queries dynamically at runtime based on user input) and then display the results in a message box, so I could see why my SQL machine wasn't working.

The results floored me. Like, "why on this earth would Microsoft think that way" floored.

I discovered (and I'm sure many of you understand this already) that "If not len(string) then" does not work the way I would think from looking at it. In fact, even "If not(len(string)) then" doesn't work in the way it appears it would. "If len(string)=0 then" works fine, as does "If string = "" then " (which I've read is the less efficient way to do this).

As if to derliberately infuriate me, "If len(string) then" (i.e. testing for the affirmative) works fine as well!

So why, oh why, for the love of all that is holy, would "not len(string)" fail to produce a TRUE result when fed an empty string?

Is this illogical to anyone else? Am I missing some important reason why this can't work? Or is it just what MS decided is the way things should/should not work?

Hi,

You may have fallen victim to a fallacy. Boolean values in Access are represented as follows:

True = -1
False = 0

Although any non-zero values are coerced to be equivalent to a True value, I am not really sure what kind of value you would get by "negating" a value of zero.

It might be a better practice to just always test for a True or False value instead of "implied" values. For example:

If Len(str)>0 Then

Or even:

If (Len(str)>0)=True Then

Just my 2 cents...

Thank you for the clarification.

Still, I am floored. Maybe I've just been out of the game for too long, but I taught myself how to program in the fifth grade (with simple stuff like QBasic) and have been dabbling my entire life. I'm certainly not a professional programmer, and probably don't always follow what a professional programmer might call "best practice", but as long as I can remember, I've "known" the following to be axiom:

0 (zero) is false
Everything else is true!

And slightly less of an axiom, but nearly equally true: the logical operator "not" is supposed to reverse the value of any boolean expression, regardless of any integers that originally generated the boolean. Not false is true and vice versa.

It's just frustrating, and something I wish I'd known getting into this. Now I'll have to scan every line of my more than 42 pages worth of code looking for similar errors, which could have been avoided if I'd been told that "Access treats booleans differently than everyone else... ever... since the beginning of time" lol.

Indeed, theDBGuy is correct. It's valid to do a "If Len(string) Then", but the converse can't be true, not without a proper logical operator, anyway. The "Not" is technically bitwise and not logical. Let's put this that way: what is the result of "Not 1"? Put it in your immediate windows:


-2 is the bitwise negation of 1. This seems bizarre but rest assured, it's not when you look at how we represent numbers. Assuming we're working with Long Integer, the value of 1 would be probably something like this:

(Note: for simplification, I'm neglecting the endianness; the actual representation is different in an little endian platform like x86)

When we negate it, the result becomes:


That looks logical, right? But why -2? Well, the leftmost bit is actually special - it's used to denote whether a number is a negative number or not. In our first example, the leftmost bit was 0, so the "1" was positive. Next question - what is the biggest positive number? This would be all 1s save leftmost bit being zero as thus:


In decimal value, that's 2147483647. So when we add one more bit, returning this:


The decimal value is now -2147483648, which is the largest negative number allowed for this size of integer. As we add one more bit, the negative numbers decrement, until we get this:


which is of course, -1. So back to us negating the binary representation of 1 - if you notice that rightmost bit is 0, so that's one value less than -1, which is of course, -2.

So when you do a "Not Len(string)", you are getting a bitwise negation, so if it's a nonzero number, it may result in another nonzero number which happens to be the two complement. So why did Len(string) work? Well, if you look at most programming languages, there's a near universal agreement (I'd say total as I do not know of any language that doesn't go by same agreement but...) that the definition of FALSE is of course, zero. What about TRUE? Well, they define it as NOT FALSE (in logical sense, not bitwise). This is why some may define True as 1, other as -1 but that doesn't matter. When we do something like "If Len(string) Then", what we are actually saying is "If Len(string)<>0 Then". Thus, True can appear to be basically any nonzero number but that's only because we found it more convenient to test for FALSE (zero) than to test for any non zero numbers.

Therefore, if you want to use implicit evaluations:

For any non-ZLS string:


For any ZLS string:


What you don't want to try and use bitwise operators as if they were logical. They seems similar but they are not same thing. Hopefully it also explains why it doesn't follow that the converse of "If Len(string) Then" won't work same way.

Then OTOH, being more explicit as theDBGuy suggests makes your code more easier to read and self-documenting.

Anyway, hope that helps.

Got that op? Such fun stuff.

Here's another thread that touches on the same subject matter. In this one a Boolean value shows one thing when you mouse over it in debug mode, but doesn't do what the mouseover says it should do!

http://www.utteraccess.com/forum/forums.html...t

I tihnk my mind must work very differently.

The Len function is designed to return a number.
Booloean logic in MS speak is 0 for false and -1 for true.

So it seems to me that len(string) should return False for an empty string, but ought to error if there IS anything in the string. Since the comparison is noether 0 nor -1, why should it work at all?

My guess is that it's trying to be 'helpful', and is actually interpreting len(string) as "not 0", as true and hence -1.




if not len(string) should return True for an empty string
(at this stage I share your wonder)


My question is why use a boolean exrpession when trying to compare it with a number?

BUT if you really want to use the boolean how about

if len(string) then
else
do what you wanted done with an empty string
endif

In which case it will do the bit that 'works', see that it doesn't have anything to do, and then operate the "else" section without further ado. - I use this approach quire often when dealing with "not".

And you may also want to think about null. Null is not the same as an empty string, and you may find that len(string) really doesn't like it at all if string contains 'null'.

Personally I also use the full expression all the time,

if len(string) = true.

and the reverse would be if len(string) = false

which may alos avoid the use of 'not'


Don't know if this is of any use, but it's meant well.

As mentioned above, when you use implicit expressions like "If Len(string) Then", it's actually same thing as saying "If Len(string)<>0 Then". If we stick to the expanded form, it should become apparent why Not does not work as we thought it would:



That's not same thing as saying:



The implicit expression "If Not Len(string) Then" is actually the former, and not latter. Thus, why it seems to work only when we test for truth rather falsehood. If we want to test for falsehood, do this instead:





I would not do that. The problem is that you're still comparing a number with a boolean. Remember that VBA will do voodoo magical conversion if you use two incongruent data types. Therefore the boolean True is coerced into an integer -1 and False into an integer 0 if compared with a numeric expression such as Len(string). Therefore, the expression is more like:


Here, we made an impossible criteria for former expression (no string could ever be len'd at -1 and thus will always evaluate as false and thus fail). OTOH, the latter is in fact valid (it's just a different phrasing of my earlier suggestion "If Len(string) = 0 Then") so we've only flipped the table but not improved our position if we use this approach.

I hope this helps.

Thanks BR:

No I wouldn't try to compare a boolean with a number either! I think it's askign for trouble - It relies too much on Access undocumented features, which may change radically from releat to release.

But that's what stigma 327 was asking about, so I tried to give him something that might work if he still wanted to work along these lines.


I had up to now thought that the implicit form was testing for true --- MS tells us that 'True' is the default, when in fact the defult is "not false".


No wonder people get confused, when the documentation is lying to us!


in oher words
if len(string)

is not the same as

if len(string) = true, (as we have been told)

but is the same as

if len(string) <> false (which is not the same thing at all)

Where did you find the documentation that says this?

Also, it may also help to realize that in start, there were no such thing as a "Boolean" data type. In C, booleans were represented as integers and it was easier to define False = 0 and everything else being not zero to be as true. Because of that, convention of using False = 0; True = nonzero numbers has been developed and became universal even when true Boolean data types were introduced in various other programming languages, including VBA. So it's actually consistent this way. So at low level, there's really no such as boolean evaluations; only integer evaluations that pretends to be booleans, just like there's no really such thing as Strings; it's just a way of pretending it's not an array of characters.