NOT FALSE better than TRUE?

[spikepl]

In queries, when specifying criteria for a boolean expression or field, what are the pros/cons, if any, for, respectively:

1. Using -1, 0
2. Using True/False as opposed to NOT False/ NOT True
3. Using Yes/No

 

[boblarson]

I find that there are times (and I'm starting to go with it more and more) when it works better for using

<> 0 or = 0

as the best method. I've seen a true/false field where -1 should get the true's but not. But turning around and using <> 0 works.

 

[Brianwarnock]

I have a vague memory, most memories are vague at my age, of reading somewhere that whilst 0 is always false and vice versa , true can be any value other than 0 and sometimes is, but as i said I cannot remember the circumstance, however Bob is correct to go with his approach.

Brian

 

[Pat Hartman]

It is best to use a positive condition rather than a negative one so you test for what you want rather than what you don't want. This avoids any issues with null values not falling where you expect them to. For example:

= 0 'will return only true values
= -1 'will return only false values
<> 0 ' will return only non-0 values but will not include null values
<> -1 ' will return only non -1 values but will not include null values

There are occassions where you will decide to make negative conditions because positive ones will be too lengthy due to the number of values you need to include. In this case, you need to deal explicitly with nulls.

(A <> 4 and A <> 89 and A <> 102) OR A Is Null

I prefer to use true and false rather than 0 and -1. Although most people reading the code or query will know what 0 and -1 mean, True and False are more clear. I never use Yes and No.

 

[boblarson]

= 0 'will return only true values
= -1 'will return only false values

I think you got that reversed.

=-1 will return only TRUE values
= 0 will return only FALSE values

And I use <> 0 because I don't have nulls in my true false unless it is an exception. And we use SQL Server for the backends and I've actually had True's not show up when using =-1 (even trying =1) but I have not had a problem with <> 0 for True.

I guess it comes down to that there is no real hard and fast rule about it because there are situations which can occur that do not fit into a certain rule. So the key is to know your data and know what fits for that.

 

[Pat Hartman]

Thanks Bob sometimes my eyes cross.

 

[bob fitz]

Pat,

In post #4 you say that you never use Yes/No. Is there something wrong with using them?

 

[vbaInet]

I would imagine Pat doesn't use it because it's not a portable data type, i.e. if you were to migrate to a more powerful db platform, Oracle etc, there's no equivalent.

As for Yes/No vs -1/0, I mostly use the Integer version because Access doesn't need to translate True/False or Yes/No to its Integer value.

 

[Pat Hartman]

vbaInet got it right. I do too much development that ends up being upsized so I avoid this datatype. The only convenience of using it is that Access will always display it as a checkbox in queries, forms, and reports. With an integer (the substitute), you need to change the default textbox to a checkbox when you develop a form or report. I don't give users access to queries or tables so there is no issue there.

The problem is that Jet/ACE support three states for the Yes/No value - Yes, No, Null whereas SQL Server only supports two - Yes and No. If your data truly is only ever true or false and you have a default defined at the table level, you won't have any trouble upsizing. However, if logically the field might be null, then you have to use the integer data type if you ever want to upsize. You could of course, wait until you have to upsize and do the conversion then. But, you'd have to remember to do it and you'd have to run an update query to get rid of any nulls in the data you intend to port to SQL Server.

 

[ChrisO]

Another aspect…

Sub TestIt()

    Fred True

End Sub


Sub Fred(ThisBoolean As Boolean)
    Dim strSQL As String
    
    strSQL = "SELECT * FROM MyTable WHERE MyBoolean = " & CStr(ThisBoolean)

    MsgBox strSQL
    
    'English works.
    'SELECT * FROM MyTable WHERE MyBoolean = True
    
    ' Dutch fails.
    'SELECT * FROM MyTable WHERE MyBoolean = Waar
    
End Sub

The above code works with an English language pack installed.
However, if a non-English language pack is installed, in this case Dutch, it will fail because SQL is parsed only in English and does not understand Waar (Dutch for True).

There are (were?) about 35 language packs available. Although it would not normally concern us it can if we place a demo in the sample database forum. People from all around the world can download the demo and try to run it under their conditions. I guess that there could also be situations where, even on the same network, two different computers are running different language packs. I’m thinking Canada where English and French may be used in the same office. Who knows, but I think they would have sorted that out.

The simplest way around it is to not use Booleans, so rather than:-
ThisBoolean As Boolean
we can use:-
ThisBoolean As Integer
and Access will use the numerical value which will be correct with all language packs.

Chris.

 

[Guus2005]

...
The above code works with an English language pack installed.
However, if a non-English language pack is installed, in this case Dutch, it will fail because SQL is parsed only in English and does not understand Waar (Dutch for True).
...
Chris.

That is unfortunate very true!
So to force a True value i sometimes use

blnUnlucky = 1 = 1 'Waar/True

Beware that the following will be unreadable:

If not blnUnlucky <> False then

Enjoy!