IIF returns 0 instead of boolean (2 Viewers)

[jal]

I have a boolean column. Originally, it would show up in my VB.Net grid as checkboxes.

The checkboxes are no longer showing as a result of the following change of code. To avoid nulls, I now do it like this:

IIF(Attended IS NULL, False, Attended)

Instead of checkboxes, I now get a column of zeroes, as though the result is no longer boolean. I even tried this:

SELECT cBool(IIF(Attended IS NULL, False, Attended))

But I still get zeroes instead of checkboxes.


(I tried the NZ function but it was throwing "Undefined NZ function" errors on one or more of my boolean columns).

Any ideas?

 

[redneckgeek]

Boolean data is stored as 0 or 1.
1 = Yes, True, or On.
0 = No, False, or Off.

 

[jal]

Boolean data is stored as 0 or 1.
1 = Yes, True, or On.
0 = No, False, or Off.

Thanks for trying to help, but I have a couple of problems with your response. First, I'm not sure Access stores True as "1". Perhaps it does so internally, but the return value is actually negative 1. For example if you type

SELECT 1=1

into sql View, this returns -1 since it equates to "True"

But more importantly, your response seems to miss the point of my question. As I said, in my original query to Access, the boolean results were indeed returned as checkboxes (not as zeroes or ones). It is only when I modified the boolean logic by using an IIF clause to return the boolean value that my checkboxes disappeared.

The question is - given that in both cases I am returning the same boolean value (whether True or False), why I am getting two different KINDS of results, and how can I fix it as to bring the checkboxes back?

 

[boblarson]

Boolean data is stored as 0 or 1.
1 = Yes, True, or On.
0 = No, False, or Off.

Sorry, that is true for SQL Server, but not Access.

Access has

-1 = Yes, True, On
0 = No, False, Off

As for getting checkboxes in a query, since I never use queries for display, it shouldn't make a difference. A form or report should be used to display what you want from a query. There you can set what control you want to show up.

As for this

SELECT cBool(IIF(Attended IS NULL, False, Attended))

You should try using

SELECT Nz([Attended], False) As YourFieldName

 

[jal]

Sorry, that is true for SQL Server, but not Access.

Access has

-1 = Yes, True, On
0 = No, False, Off

As for getting checkboxes in a query, since I never use queries for display, it shouldn't make a difference. A form or report should be used to display what you want from a query. There you can set what control you want to show up.

As for this

SELECT cBool(IIF(Attended IS NULL, False, Attended))

You should try using

SELECT Nz([Attended], False) As YourFieldName

As I said, the NZ function was throwing errors in my app (unless it was for lack of square brackets).
But more to the point, I think you can see this in Access (maybe I'll upload the data). If I paste the following query into sql view and then run it, Access displays the results on screen as checkboxes:

SELECT * FROM ShowInvitations

But if I use an IIF function, it doesn't show up as checkboxes (the column is all false, and I get all zeroes).

As for your claim that I should cause my app to use the proper control (a checkbox), one problem is a performance issue. Yes, I could add a checkbox column to my grid and then copy the values over from the original boolean columns to the checkbox column. But I am getting undesirable performance costs that way.

So I'll ask the question a third time - what's the difference between the two boolean queries? Why does an IIF return a boolean in one way, and a non-IIF in another way?

 

[jal]

I pasted the following into the editor:

SELECT NZ([InvitationSent], FALSE) FROM ShowInvitations

but I don't get checkboxes. I get zeroes. What's worse, when I run it from VB.Net using the OledB provider I get that error I mentioned before:

"Undefined function 'NZ' in expression"

Whereas, even in VB.Net, I can get checkboxes by doing:

SELECT InvitationSent FROM ShowInvitations

 

[jal]

Well, I don't have the solutoin - but this much I learned from an article: If you query Access using the OleDB provider you cannot use the NZ function because OLeDB doesn't accept it. Anyway it isn't what I want because even in Access it returns zeroes instead of checkboxes.

 

[boblarson]

As I said, the NZ function was throwing errors in my app (unless it was for lack of square brackets).

Yes, you have to use the square brackets to tell Access it is a field you are checking.

As for your claim that I should cause my app to use the proper control (a checkbox), one problem is a performance issue. Yes, I could add a checkbox column to my grid and then copy the values over from the original boolean columns to the checkbox column. But I am getting undesirable performance costs that way.

Sounds like a different problem to me. What "Grid" are you talking about?

So I'll ask the question a third time - what's the difference between the two boolean queries? Why does an IIF return a boolean in one way, and a non-IIF in another way?

IIF's, as do other functions, return values and not graphical representations. The checkbox is a graphical representation and therefore when run through a function it returns the value and not the graphical representation. If you want a graphical representation - use a form.

I will also address something else here. It would be much better and result in a lot less problems if you would share the full information up front instead of complaining that I'm (or someone else) is giving you bad information. If we don't HAVE all of the applicable information we will be going off of the assumptions that this is all being done in Access. Using an OLEDB connection implies that you are using SQL Server as a backend, which changes the information you would be receiving in return IMMENSELY. Access reacts differently when it is self-contained or linked to another Access database as compared to linked to SQL Server (or another program).

As for the Is Null versus IsNull([FieldName]), you normally would use the IsNull() function when dealing with Access, but SQL Server does not recognize that so the [FieldName] Is Null would be appropriate. The NZ function works well but you do have to use it correctly as well. When in a SQL statement in Access you need to keep the fields in square brackets (as you also need to do with IIF statements).

 

[jal]

I will also address something else here. It would be much better and result in a lot less problems if you would share the full information up front instead of complaining that I'm (or someone else) is giving you bad information. If we don't HAVE all of the applicable information we will be going off of the assumptions that this is all being done in Access. Using an OLEDB connection implies that you are using SQL Server as a backend, which changes the information you would be receiving in return IMMENSELY. Access reacts differently when it is self-contained or linked to another Access database as compared to linked to SQL Server (or another program).

First, I have no idea why you think my project is related to Sql Server.
Second, you suggested I failed to indicate that I was using another program. On the contrary the very first line of my post stated I was using Visual Basic .Net. True, I didn't mention Oledb at first because I assumed that such was implied, since Oledb is the standard provider for accessing an Access database from Visual Basic or Visual Basic .Net. You just asked me , what grid? Again, I indicated as much in the first post where I said, "A VB.Net grid." Ok, I could have said VB.Net DataGridView, but I didn't see the need to be so precise because the main point is that i was getting 2 different kinds of behavior, regardless of what grid I was using.

Fact is, I was clear enough in the first post indicating that I was getting two different kinds of behavior from the same boolean value (whether True or False). Yes, I was little annoyed by the first poster's superficial response, but my response to him wasn't any more harsh than yours was, as far as I can see.

On the other hand, if I offended anyone, my apologies.

To clarify, I am using a standalone Access 2003 database that has absolutely nothing to do with, nor any connection to, Sql Server.

 

[georgedwilkinson]

A couple of things:
1. Nz() is an Access function, not .Net. You'll never get a good response using a function that does not exist in the development environment you're working in.
2. In any modern programming language/development environments, 0 = False/No/Off and any other integer number = True.

Sub test()
Dim x As Boolean
x = True
Debug.Print x
x = 500
Debug.Print x
x = 500 And -501
Debug.Print x
x = (1 = 2)
Debug.Print x
End Sub

will yield:
True
True
False
False

3. If you EVER want a value other than True, False, or NULL (or 0, 1/-1, Null in some environments) from a Boolean, you must display it with a control that interprets it into something other than True, False, or NULL, (or 0, 1/-1, Null in some environments). In other words, if you want to see a checkbox in Access, you have to define that checkbox (either on the form, query column property, or heaven's forbid, a table column property).

 

[jal]

A couple of things:
1. Nz() is an Access function, not .Net. You'll never get a good response using a function that does not exist in the development environment you're working in.
2. In any modern programming language/development environments, 0 = False/No/Off and any other integer number = True.

Sub test()
Dim x As Boolean
x = True
Debug.Print x
x = 500
Debug.Print x
x = 500 And -501
Debug.Print x
x = (1 = 2)
Debug.Print x
End Sub

will yield:
True
True
False
False

3. If you EVER want a value other than True, False, or NULL (or 0, 1/-1, Null in some environments) from a Boolean, you must display it with a control that interprets it into something other than True, False, or NULL, (or 0, 1/-1, Null in some environments). In other words, if you want to see a checkbox in Access, you have to define that checkbox (either on the form, query column property, or heaven's forbid, a table column property).

Thanks for trying to help, but this misses the point entirely. I'm not TRYING to get back something other than True, False, 0, 1, -1.

What I am trying to understand is why Access is not consistent in its handling of booleans. Try this experiiment. Create a table with one column (Yes/No) which I take to mean "boolean". Go into dataSheet view. You'll see CHECKBOXES. Put in a few checkmarks because doing so adds a few more boolean rows.

Now go into sql View and type
SELECT * FROM Table1
Guess what'll you see? CHECKBOXES! (That's precisely what I get in VB.Net, which is what I want - hence there is no NEED for me to add checkbox controls to my grid).

Now do it this way:
Select IIF(myBooleanCol IS NULL, False, myBooleanCol)
From Table1

Now what do you get? 0's and 1's (no more checkboxes !!!).

Clearly, Access is returning two different types of values here for the same boolean result. That's not consistent. If I can't figure out WHY, I would least like to know HOW to fix it back to checkbox mode.

 

[jal]

I've uploaded a screenshot of my Visual Basic .Net grid to illustrate the point. This is not an Access screen shot. It is a screen shot of a VB.Net program that queries an Access 2003 database.

Note this grid has checkboxes. I didn't add a checkbox control to my grid. I simply selected two boolean columns from my Access table.

But if I change my SELECT to handle those columns with an IIF clause, the checkboxes disappear !!! (The colum becomes filled with -1's).

 

[jal]

I never found a solution, but it seems clear enough that:

(1) If you select a boolean column directly, Access returns a boolean value. In VB.Net, if a genuine boolean value is returned (as opposed to a number like 0 or -1), the grid displays it as a column of checkboxes.

(2) If your SELECT clause applies an IIF or other expressoin to the boolean column, Access refuses to return a boolean value - it returns a number 0 or -1. This is why the checkboxes disappeared from my VB.Net grid.

(3) The function CBool doesn't work in actual queries. It doesn't return a boolean value (True or False). It simply returns 0 or -1 and is therefore useless in most queries, since Access would have returned 0 or -1 by deafult.

I'm a little disapponted there is apparently no way for an expression to return a boolean value. At the very least we should be able to convert a 0 or -1 to a genuine boolean True or False.

 

[Banana]

I normally do not work in VB.NET but I have to side with BobLarson-

SQL statements are inherently meaningless without some kind of parser, and I would bet that it's your Datagrid, OLE DB provider or something in between that is doing the parsing. As already shown, Access represents False as 0 and anything else (with -1 as the default) as True. How it is actually represented, is entirely dependent on the provider or the form.

I'd imagine that somehow the IIf() causes the value to be something else that isn't recognizable as boolean (and IIRC, most functions uses Variant as a argument type). I'm not quite sure why CBool() Didn't work.

However, for whatever reasons, this isn't really a showstopper. For example, you can tweak the SQL to return actual 'True'/'False' instead of 0s and -1s, or tell your datagrid to interpret 0 or 1 as a boolean, which I know I can do with any Access checkbox.

 

[neileg]

Let's get something clear. Access only has three datatypes, text, memo and numeric. It doesn't have a boolean datatype and pretends that a numeric field is boolean. Dates are the same. So, when you retrieve the data, you get the number. How it displays is then up to the interface. If you are using a query, then in the properties for that field you can choose how it displays a 'boolean' value. That may solve your issue.

 

[jal]

I normally do not work in VB.NET but I have to side with BobLarson-

SQL statements are inherently meaningless without some kind of parser, and I would bet that it's your Datagrid, OLE DB provider or something in between that is doing the parsing. As already shown, Access represents False as 0 and anything else (with -1 as the default) as True. How it is actually represented, is entirely dependent on the provider or the form.

Based on the data above (see the earlier posts), it seems to me your statement is simply incorrect. Did you try the experiments I proposed? How do you explain the results of those experients? This is a case where I am not inclined to take your word for it. Show me the money. My experiments show two different types of behavior. The ONLY explanation logically plausible to me at this point is that Access is returning a boolean True or False (in the one case) and then 0 or -1 (in the other case). So your suggestion that Access by nature returns 0 or -1 is just not true, as far as I can see. The default for a boolean colum in that Access returns True or False (a genuine boolean). Access changes this default to a numeric datatype of 0 or -1 ONLY when the boolean column is used in an expression.

I'm not quite sure why CBool() Didn't work.

Which is the whole point of this post.

However, for whatever reasons, this isn't really a showstopper. For example, you can tweak the SQL to return actual 'True'/'False'

Show me how. All my efforts have failed to this ponit. Again, that's the whole point of this thread. CBool doesn't do what it purports, at least not in queries. If Access returned a boolean it would show (when you run a query in Access SQL view) as checkboxes AND it would show in my grid as checkboxes. It is doing neither in the case of boolean expressions, whereas, with a simple SELECT on those boolean columns, the checkboxes appear in BOTH places. You're dawing conclusions here but but I fail to see your evidentiary support for them. Hence I'm not inclined to continue this discussion much further. I think we'll have to agree to disagree.

You said my alternative is, "tell your datagrid to interpret 0 or 1 as a boolean, which I know I can do with any Access checkbox."
Again, the whole point of this thread is that such workarounds carry performance costs, as far as I know. (Feel free to post a solution without any performance costs). I'm dumping anywhere from 50,000 to 100,000 rows into a datagrid for the user to review, make some edits, and then dump out to Excel. When I loop through the table to set checkboxes I can see performance costs. I didn't say it was a "showstopper" as you call it. I merely said it is a bit disappointing that this database cannot return a boolean value in a boolean expression. It returns a number.

 

[jal]

To further prove the point, I just tried the following experiment in VB.Net. I rand this query from VB.Net:

Dim D As DataTable = Me.funcFillDT("Select IIF(DefinitelyAttended is null, false, DefinitelyAttended) FROM ShowInvitations")

MsgBox(D.Columns(0).DataType.ToString())

Guess what the datatype of the column is? INTEGER !!!

But if I do a simple SELECT, guess what it returns in the messagebox? "Boolean" !!!

Now, if you can show me a way to "tweak the SQL" (to use your own words) as to make it return Boolean after the IIF is applied, I'm all ears. That's why I started this thread.

 

[jal]

Let's get something clear. Access only has three datatypes, text, memo and numeric. It doesn't have a boolean datatype and pretends that a numeric field is boolean.

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.

 

[Banana]

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.

Now that makes sense. When we used IIF(), we were actually implicitly typecasting the boolean data type to integer. Like I said earlier, several functions use variant as a input data type and output, so this may contributed to skewing the view, and thus we had integer results. Source for no nulls allowed within Yes/No type, straight from horse's mouth.

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

SELECT cbool(foo) FROM bar;

will return a bunch of 0s and -1s but is otherwise correct. I bet that's because CBool() returns a VBA boolean, which isn't same thing, though it can act very like, as Yes/No field- (VBA boolean = 2 bytes; Yes/No field = 1 bit).

As for "tweaking SQL", I was referring more to presenting the output in a format that your datagrid likes (e.g. if it all want is literal 'TRUE'/'FALSE', give the datagrid just that). As I said before, I don't know anything about VB.NET, but I still perceive this as a formatting problem. Frankly, I'm surprised that there's a performance hit if you have to tell datagrid to display checked for -1 and unchecked for 0, but will take your word for it.

A quick example of tweaking to return literal True/False:

SELECT IIF([foo]=0, 'TRUE', 'FALSE') FROM bar;

 

[namliam]

2. In any modern programming language/development environments, 0 = False/No/Off and any other integer number = True.

This is true but also 0 *urm* false....

Try ?cdbl(True) this will return -1 in access. It is true that any other value than 0 will evaluate to True when converted to a Boolean, but True = -1 NOT any other value, but exactly -1