IIF returns 0 instead of boolean (1 Viewer)

[neileg]

My experiments suggest otherwise. I'm not going to just take your word for it. Both in Access (sql view) and in VB.Net I'm getting the same thing - a boolean displays as checkboxes. Access knows the difference, as does any database. Now, in the case of expressions, it chooses to return a numeric. Fine with me - but in that case we should be able to fall back on CBool when desired. Only CBool doesn't work. It does nothing. Again, that's the point of this thread.

Please yourself. You are mixing up what the interface does and what the data is stored as. You seem to be hung up on inconsistencies in the interface. Get over it, it's a Microsoft product...

 

[namliam]

it's a Microsoft product...

Hehehehe

Sad but true,,, very sad indeed, but totaly true...

 

[Brianwarnock]

I just noticed that you were talking about boolean column, and how you have to check for null values.

Are we really talking about a column with data type of Yes/No in an Access database? Because if that is the case, then that won't accept any Null values. But if you have Null values, then it may indicate to me that you are using something else that's not a Yes/No field type.

When I read the first post I thought " Can you have Nulls in Boolean? Well I know very little compared to many posters so I'd better say nothing rather than appear a fool"

So can you? If so how?

Brian

 

[jal]

Please yourself. You are mixing up what the interface does and what the data is stored as. You seem to be hung up on inconsistencies in the interface. Get over it, it's a Microsoft product...

Baloney. True, if I only were basing my conclusion on one scenario, it would be a bit facile. But I confirmed it in other ways. For example I showed above that Vb.Net returns datatype "Boolean" EXPLICITLY in the one case and returns "Integer" EXPLICITLY in the other case. Whereas you're just making unsupported statements.

This is not say to that I am 100% sure of anything. But for you to pretend that my argumens lack cohesion and support is gratuitous. You yourself offer no cohesive explanation of the phenomenology indicated in my accounts on this thread.

 

[jal]

When I read the first post I thought " Can you have Nulls in Boolean? Well I know very little compared to many posters so I'd better say nothing rather than appear a fool"

So can you? If so how?

Brian

Good question. I don't know if a boolean column would "normally" be capable of having nulls (haven't done any testing) but in my scenario it seems unavoidable. Let me explain. I am doing a left join. That means that in cases whether there is no matchup, part of the joined row (several of the columns) will be null. Some of these are boolean. But at least they were showing up as checkboxes, until I used an IIF clause to deal with the nulls whence the checkboxes disappeared because the IIF returned a numeric (integer) instead of a boolean.

Hence this thread.

 

[Banana]

jal, why not just IIF only the columns that will have null values, and vanilla select the booleans?

 

[jal]

I also discovered that Cbool works; just not as expected.

It's returning a numeric datatype (integer), not a boolean datatype. In what sense? Perhaps I can clarify this. Both In VB.Net you can create any kind of custom datatype, for example a football player:

Private Class FootBallPlayer
FirstName as String
Lastname as String
TeamName as String
Age as Integer
Salaried as Boolean

End Class


And so on. (In VB it's called a Type, but VB.Net calls it a class). You can even nest classes within classes. You can make potentially infinitely complex datatypes. So those who think computers only "understand" 0's and 1's are not understanding properly. To play electronic football across the internet, my computer sends footbal players to my opponent, and his computer recognizes the incoming data as "football players." BAsed on this paradigm, a computer can send and receive datatypes potentially of infinite complexity.

Guess what? Since my computer can clearly distinguish one complex objects like a football player from another complex object such as a game official (an umpire/ referee), obviously it can distinguish between SIMPLE datatypes such as integer, string, and boolean. There is no NEED for Access to return a number in every potentially boolean scenario. In fact a boolean is arguably the simplest and most universal of all datatypes because it mirrors ON/OFF computer circuits.

Therefore if CBool returns a number (which it does), it is clearly not working. It should be returning a boolean. To prove the point, type the following into sql view:

SELECT Cbool(0) INTO NewTable

Now take a look at NewTable - it will have one column. Is the datatype "Boolean"? No - it's numeric !!!

 

[jal]

jal, why not just IIF only the columns that will have null values, and vanilla select the booleans?

I wanted ALL my columns to have checkboxes without having to write an extra 100 lines of code. Microsoft has been at this for 3 decades - am I really asking too much to want from them a simple boolean, for heaven's sake?

This is not to poo-poo Microsoft. I love what they do. I'm just mentioning a minor beef and hoping someone has a solution which I, to this point, have been unable to find.

 

[Banana]

jal,

In case you haven't noticed, we're in agreement about how Cbool behaves. We got the same results in our tests.

Like it or not, VB/VBA, and I'd bet VB.NET has a tendency to do lot of implicit typecasting, and several built-in functions returns variants, which may not always resolve to a boolean type, and I've already pointed out that Access's Yes/No field type is not same, although similar, to VBA's boolean data type.

Also, I don't understand why you think there'll be 100 lines of code to do a vanilla selects of boolean columns and IIF/ISNULL'ing the rest. Surely not like this:

SELECT boolean1, boolean2, boolean3, IIF(ISNULL(ColumnWithNulls1, 0, ColumnWithNulls1), IIF(ISNULL(ColumnWithNulls2, 0, ColumnWithNulls2) FROM foo;

Last time I checked, SQL doesn't handle custom data types, unless you store it as a BLOB, so it really doesn't matter how you have your UDT set up.

 

[Brianwarnock]

.... I am doing a left join. That means that in cases whether there is no matchup, part of the joined row (several of the columns) will be null. Some of these are boolean. But at least they were showing up as checkboxes, until I used an IIF clause to deal with the nulls whence the checkboxes disappeared because the IIF returned a numeric (integer) instead of a boolean.

Hence this thread.

When they were showing up as checkboxes weren't they unchecked ie False ?
Therefore why is the IIF necessary?

Brian

I've just realised that Banana said this on post 26

 

[neileg]

Baloney. True, if I only were basing my conclusion on one scenario, it would be a bit facile. But I confirmed it in other ways. For example I showed above that Vb.Net returns datatype "Boolean" EXPLICITLY in the one case and returns "Integer" EXPLICITLY in the other case. Whereas you're just making unsupported statements.

No I'm not. I'm answering from experience. You never get to see the raw data in an Access table, you always see it through the interface. Even when you view a table, you are actually seeing a query based on the table. That's why when you apply a sort to a table it doesn't sort the data, just the display.

This is not say to that I am 100% sure of anything. But for you to pretend that my argumens lack cohesion and support is gratuitous. You yourself offer no cohesive explanation of the phenomenology indicated in my accounts on this thread.

I didn't make those accusations. I said you were mixing up the data and the interface. That's a specific point, and it remains true and I am certainly not 'rubbishing' your arguments. But I'll be more explicit. Any time you try and look at the data in a table you are getting back what the interface reports, not the data itself. I might be dissapointed that the behaviour differs depending on how you enquire on the data, but I'm not surprised.

The default value for an Access checkbox is zero. Not null and not anything else. This makes it different from a plain numeric field which defaults to null. When you check the checkbox, the value becomes -1, and not anything else. When you uncheck it again, the value returns to zero. It's never an explicit boolean, it's a numeric representation.

 

[Banana]

FWIW, Mr. Browne swears off Yes/No fields and advises using Numeric fields.

 

[redneckgeek]

FWIW, Mr. Browne swears off Yes/No fields and advises using Numeric fields.

Very interesting read... but I can see some cases where I'd disagree -
especially if the database is properly designed/normalized. Not that I'd ever care to debate Allen Browne on database design.

 

[neileg]

Has the OP given up?

 

[jal]

jal,

In case you haven't noticed, we're in agreement about how Cbool behaves. We got the same results in our tests.

Like it or not, VB/VBA, and I'd bet VB.NET has a tendency to do lot of implicit typecasting, and several built-in functions returns variants, which may not always resolve to a boolean type, and I've already pointed out that Access's Yes/No field type is not same, although similar, to VBA's boolean data type.

Also, I don't understand why you think there'll be 100 lines of code to do a vanilla selects of boolean columns and IIF/ISNULL'ing the rest. Surely not like this:

SELECT boolean1, boolean2, boolean3, IIF(ISNULL(ColumnWithNulls1, 0, ColumnWithNulls1), IIF(ISNULL(ColumnWithNulls2, 0, ColumnWithNulls2) FROM foo;

Last time I checked, SQL doesn't handle custom data types, unless you store it as a BLOB, so it really doesn't matter how you have your UDT set up.

I've been too busy to monitor this thread, but I am back for the moment.


I'm not sure I understand this post. As I have indicated several times, I originally started out using the code you suggest. The problem is that I was trying to avoid ALL nulls because my code was getting complex in some places (I was doing quite a bit of complex comparisons and manipulations of datatable columns and values) and, as a result, the nulls were very bug-prone. I am already stressed out enough over my project - I don't need another source of bugs. Hence I converted the simple selects (ALL of them) to IIF functions to eliminate nulls. But in so doing, my checkboxes disappeared. Hence this thread.

 

[jal]

FWIW, Mr. Browne swears off Yes/No fields and advises using Numeric fields.

Very useful information. Thank you.

 

[datAdrenaline]

Jai ...

The reason is simple ... JET ALWAYS returns a numeric value for boolean results, it is the Access UI that gives you the check box ... You see, you get the check box when viewing the field directly because Access will use the control type specified in the "Display Control" property of the field being referenced, which is defined on the "Lookup" tab of that field in table design view. Yes/No fields DEFAULT to "Check Box" for that property. As soon as you put the field in question into an expression ... you no longer have the privelege of that default "Display Control" because Access can not associate the expression you created with the field(s) it contains in order to get that property ... so ... you get the universally compliant display control of a Text Box for ALL expression, regardless of the "Display Control" property for the field(s) within the expression. Does that make sense?

By the way, if you use Nz() in Access ... be prepared, because Nz() returns a Variant to the JET expression service, the JET expression service coerces Variants to Strings ... (note the cell alignment of the result of a Variant expression in an Access query)

One more thing. There is absolutly NO NEED to coerce a Null Yes/No field to false. A Yes/No field will ONLY store two states ... True and False. Nulls are coerced to False before being saved in the table.

EDITS ....
I did not read all the posts in their entirety before posting ... for some reason I missed the fact that is was up to 3 pages ... so any info that I restated, I apologize for the duplicity ... but the crux of my post is ... Access (NOT JET) gives you the check box through the fields definition from the table def, once you remove that direct association by placeing the field in an expression, Access gives you a text box to visualize the data.

 

[datAdrenaline]

By the way ...

CBool() returns a boolean result (VarType of 11) ... but, as stated above, will be visualized with a text box in Access when viewed in a datasheet of a query.

 

[Brianwarnock]

One more thing. There is absolutly NO NEED to coerce a Null Yes/No field to false. A Yes/No field will ONLY store two states ... True and False.

Brent as you say you haven't read all of the posts and the above has been mentioned before, however I did mess about with a little DB and although all tests on the "NULL" return show that it is treated as False
eg Expr3: IIf([fldboo]=False,"F","T") ,
if the result is taken into a TOTALS query and say you want to sum the TRUE/FALSE by date, don't ask me why it was just a bit of fun , then the date with no data returns a NULL
ie Sum(tmst.fldboo) AS SumOffldboo does not return 0
and further more if you run a query against this
IIf([sumoffldboo]=0,"F","T") AS Expr3, expr3 returns T ie it returns the false ,
nz([SumOffldboo],9) AS Expr4, returns 9 , so it is NULL.

Guess who had too much time on his hands , I'm not clever/knowledgeable enough to draw any conclusion other than to agree with Mr Browne, whose audit trail I used when I worked.

Brian

 

[datAdrenaline]

Hey Brian ..

>> although all tests on the "NULL" return show that it is treated as False <<

The help even says as much in its description of the If..Then VBA construct. But I think there is a distinction that needs to be addressed. It is a Null RESULT of an expression that is treated as "False" as apposed to the Null value itself ...

To illustrate ... lets look at this ...

If [COLOR=blue]Null = True[/COLOR] Then
    Debug.Print "Null is True"
ElseIf [COLOR=blue]Null = False[/COLOR] Then
    Debug.Print "Null is False"
Else
    Debug.Print "Null is neither true nor false" 
End If

The above will return a "Null is neither true nor false" to the immediate window since both expressions in blue return a Null result. Now ... I do realize that you can do this to ...

If [COLOR=blue]Null [/COLOR]Then
    Debug.Print "Null is True"
ElseIf [COLOR=blue]Null [/COLOR]Then
    Debug.Print "Null is False"
Else
    Debug.Print "Null is neither true nor false" 
End If

Which is essentially what my first code block boils down to, but in this case Null is not being used as a distinct value ... its being used as an Expression and the result of that expression is Null ... so program execute follows the same path as if the expressions result was False.

You can apply the same type of "thing" with IIf() ...

IIf(Null = True, True, IIf(Null = False, False, "Unknown"))

The text "Unknown" will be returned simply because Null does not equal True or False, but program flow is that which would be taken if the expression returned a False.

So ... to provide me a distinction (since Null is neither true nor false), I like to refer to a Null result as something other than True or False and thus the "Else" branch is taken (ie: Null is "Else"). I convey things that way simply to prevent folks from associating Null as equal to False, which is far from the truth.

... So ... we are both saying similar stuff and we both seem to understand Nulls !!! ... Yippeee!!! ...