When is a boolean field false?

[John Sh]

I have a table of some 11975 records. A boolean field called "label" defaults to "No", in a table called "All"
When I run the attached code it returns11975 positive results.
I use this field to trigger printing of a report and the report only prints the records with the "label" field checked.
I then clear all checked fields and run a slightly different version of the code to indicate all 'label" fields are negative but the code always responds with a positive field exists.
I have tried a copy, new record, paste, delete original and rename the copy but still get the same result.
Looking at the table on a form, the "label" field shows "No" unless it is ticked.
What am I doing wrong?

Public Function isLabel()
Dim rs As Recordset
Dim rsInt As Integer
rsInt = 0
Set rs = CurrentDb.OpenRecordset("All", dbOpenSnapshot, dbReadOnly)
rs.FindFirst rs!Label = True
Do While Not rs.EOF
rsInt = rsInt + 1
rs.MoveNext
rs.FindNext rs!Label = True
Loop
End Function

If I run this same code with "true" replaced with "false" rsInt returns 5989. What gives???

 

[Gasman]

I do not see where you set a value for the function to return?

If the first FindFirst does not find a true label, wouldn't you be at EOF?

 

[theDBguy]

I have a table of some 11975 records. A boolean field called "label" defaults to "No", in a table called "All"
When I run the attached code it returns11975 positive results.
I use this field to trigger printing of a report and the report only prints the records with the "label" field checked.
I then clear all checked fields and run a slightly different version of the code to indicate all 'label" fields are negative but the code always responds with a positive field exists.
I have tried a copy, new record, paste, delete original and rename the copy but still get the same result.
Looking at the table on a form, the "label" field shows "No" unless it is ticked.
What am I doing wrong?

Public Function isLabel()
Dim rs As Recordset
Dim rsInt As Integer
rsInt = 0
Set rs = CurrentDb.OpenRecordset("All", dbOpenSnapshot, dbReadOnly)
rs.FindFirst rs!Label = True
Do While Not rs.EOF
rsInt = rsInt + 1
rs.MoveNext
rs.FindNext rs!Label = True
Loop
End Function

If I run this same code with "true" replaced with "false" rsInt returns 5989. What gives???

Hi. I don't think I understand what you want that function to do. Is it supposed to count how many Label=True? If so, when do you execute it? Was it after you set all the Label=False? Can you show us that code too?

In the code you posted, this line may not be doing what you think it may doing.

rs!Label=True

If the record has True in the Label field, that code will resolve to:

True = True

Otherwise, it will resolve to:

False = True

Sent from phone...

 

[arnelgp]

you need to check for .NoMatch on the recordset and not .EOF.
you also do not move the recordset.

btw it is easier to DCount() rather than use recordset:

Public Function isLabel()
Dim rs As DAO.Recordset
Dim rsInt As Long
rsInt = 0
Set rs = CurrentDb.OpenRecordset("All", dbOpenSnapshot, dbReadOnly)

with rs
.FindFirst rs!Label = True
Do While Not .NoMatch
rsInt = rsInt + 1
.FindNext rs!Label = True
Loop
.close
End With

End Function


using DCount()

Public Function isLabel()
Dim rsInt As Long
rsInt = DCount("1", "All", "Label=-1")
End Function

 

[moke123]

I use this field to trigger printing of a report and the report only prints the records with the "label" field checked.

Just want to point out that if this is a shared application you run the risk of collisions with other users using this method.

 

[John Sh]

I have solved my problem with a grouped make table query then count the records in that table. It's either 1 or 0. This is much faster and more reliable than using a record set. I was not looking for code corrections as much as an explanation of the results. Checking for true, positive, checked etc resulted in every record being counted. running the exact same code and checking for the opposite situation it returns just under 6000 records.
How can a boolean field be both positive and negative at the same time or is this some quirk of them being "NULL"

 

[John Sh]

Moke123 said "what I do have are a very particular set of skills, skills I have acquired over a very long career. Skills that make me a nightmare for people like you. "
What he/she should have said is""what I do have IS a very particular set of skills, " is that annoying, or what!

 

[theDBguy]

How can a boolean field be both positive and negative at the same time or is this some quirk of them being "NULL"

Hi. Glad to hear you found a solution.

A boolean field, in Access, cannot have a Null value (not like it can in SQL Server). Did you understand what I said about your code using rs!Label=True? I was thinking you probably meant to use "Label=True" instead.

Good luck with your project.

 

[The_Doc_Man]

OK, to answer the direct question: When is a Boolean field FALSE?

Some Y/N field is TRUE when its value is not zero and FALSE when its value is zero. A Y/N field is actually something called a TYPECAST of a BYTE integer; that is, it is an alternate interpretation of the field's actual value. The intrinsic values associated with TRUE and FALSE are -1 and 0, respectively. It is possible to allow a Y/N field to be tri-state, which would allow it to be TRUE, FALSE, or NULL. The problem with NULL is that it is neither TRUE nor FALSE. In fact, it isn't anything at all. And the catch is that NULL is NEVER EVER AT ANY TIME equal to anything else - including another NULL. That is, the statement X = ( NULL <> NULL ) is TRUE.

 

[moke123]

Moke123 said "what I do have are a very particular set of skills, skills I have acquired over a very long career. Skills that make me a nightmare for people like you. "
What he/she should have said is""what I do have IS a very particular set of skills, " is that annoying, or what!

 

[John Sh]

Message received and understood.