By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Booleans and Conditional Code

PhilOfWalton
Expert 100+
P: 1,430
@NeoPa.

I hate arguing with you, as you are an infinitely better programmer than I am, but I do not like the assumption that the value of ([Alive]) is True. I think it is much clearer when reading the code to state it's value :- "Alive = True".

([Alive]) is fine for experienced programmers, but not for a lot of subscribers to this site who have not been using Access for as long as we old boys have.

Phil

{This thread was split off from How to Deactive a Record in a Table.
-NeoPa.}
Jun 24 '18 #1
Share this Question
Share on Google+
12 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,282
Phil,

We should always keep in mind that this forum aims to develop young Padwans into experienced Jedi Knights. We don’t teach children to hold a pencil incorrectly with the aim of teaching them correctly at some time in the future.

In this case NeoPa’s advice teaches the OP to look at Boolean values as complete entities. This concept also allows us to use those entities elsewhere in our projects. For example, in my younger (stupider) days, you would see code like this:

Expand|Select|Wrap|Line Numbers
  1. If Me.chkAlive = True Then
  2.     Me.cmdDoSomeTask.Enabled = True
  3. Else
  4.     Me.CmdDoSomeTask.Enabled = False
  5. End If
However, NeoPa helped me understand that the more effective, straightforward and elegant way of doing the same thing is this:

Expand|Select|Wrap|Line Numbers
  1. Me.cmdDoSomeTask.Enabled = Me.chkAlive
Conceptually, this is not an advanced understanding of VBA—just a more straightforward approach.

BTW, NeoPa needs no one else to defend his advice. He’s forgotten more about Access than I will ever know.
Jun 25 '18 #2

Nauticalgent
P: 92
Not that Phil needs any of my assistance, but I believe he was instructing the OP to use "Yes" or "True" in the underlying query grid and not in any VBA code associated with Form events.
Jun 25 '18 #3

PhilOfWalton
Expert 100+
P: 1,430
@ Nauticalgent

Spot on. The OP was asking about Queries.

@ Twinnyfo

To a certain extent I agree, but I recall "Learn to walk before you learn to run"

Perhaps we should show the "exploded version of the code and then come up with the "One Liner" that the "Experts" would use.

Phil
Jun 25 '18 #4

twinnyfo
Expert Mod 2.5K+
P: 3,282
Ah, yes! I see my oversight. Again, my leaning toward VBA.... and our general reliance on the Access Query Editor.

The SQL statement:

Expand|Select|Wrap|Line Numbers
  1. WHERE ([Alive])
is converted in the Access Query Editor to:

Expand|Select|Wrap|Line Numbers
  1. WHERE ((([Alive]) = True))
Technically (I've checked), MS Access converts the above statement to

Expand|Select|Wrap|Line Numbers
  1. WHERE ((([Alive]) <> False))
In this case, both are correct (all three, in fact). The first is an SQL statement written freehand, the second is what the OP would enter into the query editor, the third is Access's interpretation of the SQL.

Good catch, Nauticalgent!
Jun 25 '18 #5

PhilOfWalton
Expert 100+
P: 1,430
Interesting.

The implication of the third example is that Null is treated as True.

Phil
Jun 25 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 3,282
Indeed, Phil. I thought the same thing. It is extremely rare, though, to set a Yes/No field to Null, even though Access allows for it in their Table structure. In VBA, however, a Boolean variable (e.g., a Yes/No Field) cannot be set to Null.

There are a few quirks like this that sometimes don't square across the application.
Jun 25 '18 #7

Rabbit
Expert Mod 10K+
P: 12,365
So a clarification is in order.

A not equals will not return nulls.

Given:
Expand|Select|Wrap|Line Numbers
  1. 1
  2. 2
  3. null
And the query:
Expand|Select|Wrap|Line Numbers
  1. select * from table where field <> 1
The result will just be the value 2. The null will not be returned.
Jun 25 '18 #8

NeoPa
Expert Mod 15k+
P: 31,489
Thank you for your comments Twinny.

However, I feel I should explain my reasoning as well as why Access converts ([Alive]) to ([Alive] <> False).

I'll start in reverse. While True, in our understanding, is a simple Boolean value, the VBA and Jet/ACE representation of True is actually an integer. So, we have True == -1 (In signed integers this is stored as a value with one-bits in every position.) and False == 0 (In signed integers this is stored as a value with zero-bits in every position). Now, in the real world integer values, as well as expressions resulting in integer values, can obviously have values which are ingeteger - but nevertheless neither of these two values. That's the first part handled. Remember this in a while.

In programming when you use If or IIf(), either in VBA or Jet/ACE/any other SQL, the branch is actually determined not on True and False, as you may expect, but on zero (False) or Not zero (Any other value). That's because it treats any value with any set bits in it as not False. Now, remembering back to the previous paragraph, any value that's neither True nor False is possible. Such a value would send the code down the non-zero fork, yet it certainly isn't True. Hence, Access converts ([Alive]) to ([Alive] <> False) (([Alive]=True) would give a very confusing and wrong result). I hope that explains what might otherwise be a little hard to understand.

By the way, the Null situation is a complication in its own right, but it's good to explain it here anyway. Any numeric expression that has an unconverted Null in it (IE. Not NZ([X],0).) will resolve to Null. In conditional code this will always take the False path. Particularly helpful to understand are :
Expand|Select|Wrap|Line Numbers
  1. (Null = True)  ==> Null ==> False path
  2. (Null = False) ==> Null ==> False path
Now, as for using ([Alive]) rather than ([Alive] = True), one obvious reason for doing that is that it's simply incorrect - as explained by the answer to the other question. For me though, the answer is more fundamental than that. It simply shows a much more basic understanding of what you're doing. There is no more need to say ([Alive]<>False) than there is to say (([Alive]<>False)=True). The misunderstanding arises from the incorrect assumption that Ifs and IIf()s require an expression. That's rubbish and always has been. It's been assumed by people who saw some examples and assumed they knew the story. It's a wrong story. The parameter or value required in these cases is simply a Boolean. Any expresion that returns a Boolean, or even an integer that can be treated as a Boolean, is fine - but the requirement for the value is only that it's Boolean or can be treated as such. Taking a Boolean, and converting it into another Boolean using an expression simply because one misunderstands the requirement, just advertises one's ignorance. Nor does it help new users, any more than taking them up the macro path before telling them they have to start afresh and try to forget what you told them before and start using VBA. Neither makes sense.

I particularly don't see any possible reason why learners should learn to walk with their knees tied together before they're allowed to run. It's inhibiting and unhelpful to learn about Booleans the wrong way - first or at any other time.

No-one's expecting new users to produce sophisticated code that shows off such understandings, but I believe it's encumbent on us to teach them the right way from the start and so give them a better chance of understanding what they're doing and why.
Jun 25 '18 #9

NeoPa
Expert Mod 15k+
P: 31,489
@Phil.

I'd like to add that, while I disagree with you quite fundamentally on this point, I love that you raised it. We should discuss why we do things the way we do - especially if we do it differently among ourselves.

I hope you don't mind being disagreed with. Personally I don't find it disagreeable at all. I love the opportunity to air ideas and explanations.
Jun 25 '18 #10

PhilOfWalton
Expert 100+
P: 1,430
Thanks for a brilliant explanation. Fortunately all my code seems to work, but I get the point for the future.

It's never too late to teach an old dog new tricks.

Anyway, a forum like Bytes is, apart from helping people out, is a place where we should be airing our different views. There are many ways to skin a cat in Access, but our disagreements usually result in sound advice as to the best method.

So let's all continue to share our thoughts.

Phil
Jun 26 '18 #11

twinnyfo
Expert Mod 2.5K+
P: 3,282
NeoPa,

I particularly want to thank you for the explanation in Post #9. This helps me understand what goes on under the hood, and I have used this in the past to my benefit.

As to the discussion and differences of opinion, I think NeoPa is well aware that I take no offense at being wrong. He has pointed out my wrongness on enough occasions. However, as a note to all, learning Access is a lot like learning life. If we are not teachable, we will continue to struggle with things and come upon road blocks and obstacles which we, ourselves, have created. I know, beyond a shadow of a doubt, that I don't know everything there is to know about MS Access (and even though sometimes it seems like he does, NeoPa would probably admit he does not know everything). I enjoy learning more about my craft and understanding the nuances of the tools I am using. What I do makes my boss look good--so, he doesn't try to stir my chili. Much of my success, I can humbly admit, is due to this forum and the ideas we throw around--even when we disagree.

Grace and peace to all!
Jun 26 '18 #12

NeoPa
Expert Mod 15k+
P: 31,489
TwinnyFo:
I know, beyond a shadow of a doubt, that I don't know everything there is to know about MS Access (and even though sometimes it seems like he does, NeoPa would probably admit he does not know everything).
Oh you can certainly believe that my friend. I'm learning all the time.

I've found one of the best ways to learn something is to teach others. Sounds a little 'Cart before the horse' I know, but it really works. In life just as in technology.
Jun 26 '18 #13

Post your reply

Sign in to post your reply or Sign up for a free account.