Convert boolean fields to integers / keep null's null (AC2007) (1 Viewer)

Hi there,

I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)

So something like this :


However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.

So I tried this :


However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)

Any suggestions for how I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?

Only thing I can think of is to use (untested) :


But there's a number of boolean fields in there, all requiring the same treatment. Is there a quicker / more efficient method?

Thanks!

Al

What makes "quicker"/"efficient" an issue?

I was under the impression that using IIf's was a bit slow... I may be wrong...

Having said that - I tried using the IIf([tbl].[fld] Is Null, Null, CInt([tbl].[fld])) method anyway, but I'm still getting exactly the same output. Everything is either a 0 or a -1

I can't distinguish the genuine False items from the Nulls.

You don't get the nulls in the left join because in the db booleands do not support nulls - you have to go back to the original basic tables and make changes there.

The reason why I asked is that many obsess about "efficiency" and spend a lot of effort making their app run in 0.001 sec in stead of 0.00098 etc ... If you have a huge data set and lots of repeated processing that's one thing, but in most cases just move on and spend you time more creatively.

And if you can't change original tables, make queires with integer (or byte) field, set the value according to your original boolena in each, and then do the join on queries, now using your number field.

IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))

Click to expand...

I see two problems. First, I think the correct syntax for the predicate should be

IsNull( [tblTable2].[fldBooleanField] )

Second, I would have to look this up to be sure, but I think "Null" used in the context given (as a keyword) is really the ASCII character NULL, which is an integer = 0. So your statement asserts the integer 0, not a null. I.e. you cannot assert a Null in the database sense via the keyword Null.

I recall a discussion thread that pointed out that a Null cannot appear everywhere. It can only appear in variables of type VARIANT, and then only in limited contexts due to the difficulty of declaring the "value" of null.

Therefore, I suggest considering a flag in your JOINed record that is defined as 0 or 1 to show that a record was or was not found matching the JOIN criteria. Your IIF would certainly let you do this in an SQL statement:

IIF( IsNull(some field that is never null), 0, 1)

Then if you didn't load a matching record you get a 0 but if you did load something you get a 1 in that slot. Instead of looking for a null value in field X, test whether your "flag" came back as 0 or 1.

WARNING: If you use a database variable and do a DBVar.EXECUTE, the IIF won't work. You have to use a DoCmd.RunSQL to use it.

I would simply multiply by 1:

Null * 1 = Null
True * 1 = -1
False * 1 = 0

Thank you all for your valued input and opinion!

vbaInet... I tried your methodology of simply multiplying each field by 1. However, I'm still getting 0's for both genuine False records and "expected" Null records. It seems as if the query is evaluating the source field as a boolean first and determining nulls to be false before it integerizes them. Pity because this would have been a very neat workaround!

spikepl... I understand your thinking, however I'm keen not to convert the underlying BE boolean fields to be tristate, if at all possible. Primarily because these fields should really only hold a genuine True / False value for each record that exists. I don't want any ambiguity around these values. Now having said that, I do appreciate that I can mitigate against that by designing my interface to preclude Null values as an acceptable input. It's more for my own sanity that such values are restricted by the table structure itself, as well as my own interface design.

I'm curious of your suggestion to design the query with an integer / byte field "up-front" - can you help me with the SQL syntax to pre-define an output field to be a particular type, when the source field differs (i.e. boolean / bit > integer / byte?)

This was something I considered but couldn't figure out how to execute? I have used the ALTER statement to change a field type in a temporary table prior to appending records but have never managed to do it in a query? Is this possible and, if so, how so?

The_Doc_Man... Some great information here, thank you so much for the detail. I am using a database variable but I'm reading the results of the query into a recordset, so the command is actually dbs.OpenRecordset(strSQL) where strSQL is my constructed SQL query.

To your suggestion, and specifically this point :

IIF( IsNull(some field that is never null), 0, 1)

Click to expand...

Technically, the boolean fields themselves are never null (see the point above to spikepl - if a record exists, the boolean field is implicitly either True or False; they are by definition incapable of holding tristate values) So I'm struggling to see how this is any different to what I am currently doing?

Just for my own clarification - you are suggesting that, in each case where a boolean field may or may not be present as a result of a LEFT JOIN on a related table, that I should first return a flag to determine if a related record exists, then use the value of that flag in generating the value of boolean field?

Therefore, if I understand you correctly (and apologies as I could very well not be!..), you are suggesting something along the lines of :


Is this not broadly the same as :


(i.e. should I simply replace Null with a ZLS?)

Or does your suggestion require the Flag to be determined based on the 'nullness' of a non-boolean field in the same related/left-joined table?

Thank you all once again for taking the time to help me with this, it is hugely appreciated!

run query2

And you'd still be better off if your booleans were byte to start off with - nobody said anything about making that field tristate in the original tables, only in the query results.

Resolved!!

Thanks spikepl for the demo db - I got what you were saying but the example made it clearer and made implementing that method so much easier.

So basically I do my null check on a non-boolean field within the joined table (I use the primary key which is a basic autonumber identifier but I guess any non-boolean non-null field would suffice) and use that as my determinant rather than the boolean field itself.

Á la :


Working perfectly!

Huge debt of gratitude to all three of you for helping me reach this conclusion! Hopefully this will aid others with a similar issue!

Thanks again!

Al

Is this data coming from a SQL Server or Oracle db?

If I were you I would maintain the Null otherwise you'll end up with a string type:

It's coming from an Access BE, although I'm hoping, if it is successful in the long term and I can argue the case, to move it to SQL Server at some point in the future

I'll test it with Null over ZLS and see if that causes any problems; if not, I'll make the switch. Thanks vbaInet!

I just remembered this, see below what you're experiencing:
http://allenbrowne.com/noyesno.html

Best to use a Number(Byte) field as spikepl mentioned, it's what I used to use instead of Yes/No.

Wow. Just... just wow

Thanks mate!

No problemo chappy!