Check Boxes + Sort by Condition in Access Report

[jimc52]

Hello Everyone: I am hoping one of the gurus here will give me some help.
I have designed a form with some check boxes. I put the checkboxes there on the form so later I could use them as flags on separate reports. Just to answer a few questions, yes, the subform is properly tied to the main form and they are all bound controls...been there, done that. Now, the purpose of a check box, as I see it is to have two logical conditions (checked = logical 1 and unchecked = logical 0 ... either it is checked or it isn't. Theoretically to me, that means, I could set up a logic condition in a query which says, "querry only the information from the main form or subform which has one or the other condition)...then, build a report based on the query which will print out or show only the recordset which meets the criteria. In other words, I should be able to build a logic statement or SQL statement in the query which says, only get the information from the records which contain Checkbox1 = 0 (or Checkbox1 =1)...
without the OR condition of course.

The only problem with this is, I have been unable to get the query to do that.
Microsoft HELP is dumb on this issue. The logical 0 or 1 is the same thing as a logical Y or N based on Y/N. Yet, I cannot find anything that tells me how to build the SQL statement or logic statement in the query that successfully pools together only th data from the db that meets the logic criteria.

When I try to do this in a report, my fields come up blank and I have a single greyed out check box on the report. I have gone back to look at the properties of the checkboxes to see if there is any way I could build the expression from that vantage point, but there are no properties that I understand that directly do this.

So to condense what I have said, I have checkboxes on my subform which is tied to a form (yes, the field names and conditions are identical for both underlying tables), I want to end up with a report (query or not), which pprints out only the dataset which meets the logic condition of Y or N (0 or 1).

Can someone help me with the code or method to do this? Thank you everyone.

Jimc52

[MMcCarthy]

Hello Everyone: I am hoping one of the gurus here will give me some help.
I have designed a form with some check boxes. I put the checkboxes there on the form so later I could use them as flags on separate reports. Just to answer a few questions, yes, the subform is properly tied to the main form and they are all bound controls...been there, done that. Now, the purpose of a check box, as I see it is to have two logical conditions (checked = logical 1 and unchecked = logical 0 ... either it is checked or it isn't. Theoretically to me, that means, I could set up a logic condition in a query which says, "querry only the information from the main form or subform which has one or the other condition)...then, build a report based on the query which will print out or show only the recordset which meets the criteria. In other words, I should be able to build a logic statement or SQL statement in the query which says, only get the information from the records which contain Checkbox1 = 0 (or Checkbox1 =1)...
without the OR condition of course.

The only problem with this is, I have been unable to get the query to do that.
Microsoft HELP is dumb on this issue. The logical 0 or 1 is the same thing as a logical Y or N based on Y/N. Yet, I cannot find anything that tells me how to build the SQL statement or logic statement in the query that successfully pools together only th data from the db that meets the logic criteria.

Solution: In Access the logical state for true is -1 not 1.

Note: There are actually three states for a checkbox (Checked) -1, (Unchecked) 0 and (Unselected) 1.

You shouldn't come up against this problem with individual checkboxes because access defaults to 0 but look out for it in option groups.

When I try to do this in a report, my fields come up blank and I have a single greyed out check box on the report.

The aforementioned unselected state.

[jimc52]

McCarthy: Incredible! Just incredible. I NEVER would have guessed that Microsoft would bastardize simple logic algebra like that. Although the idea is ingenious, taking into account the negative 1 as a third conditional state, it defied my logic. I programmed for years in a number of languages and I didn't run into something quite like this. Access is a different kind of animal, even in it's logic elements.

I want to thank you for telling me the correct logic states. I won't be able to check this out until Monday at work, but I sure am going to give it a try.

I would like to ask you one further question, if you can answer it. In my query, under Condition, what would you recommend being the conditional logic statement? All I can find so far is using the IiF conditional statement using the field name such as IiF("One_Is", -1) In other words, if my checkbox is TRUE (Yes Conditon) then filter for all records in the record set which meet this condition. I am not sure that I have written this correctly since I normally don't use SQL statements to do things. Do you have a recommendation?

Thanks again.
Jimc52

[MMcCarthy]

Jimc52,

Can you post the relevant sql of your query as it stands at the moment. Don't worry if it's not working it will just help me to understand what you need from the logic.

Mary

McCarthy: Incredible! Just incredible. I NEVER would have guessed that Microsoft would bastardize simple logic algebra like that. Although the idea is ingenious, taking into account the negative 1 as a third conditional state, it defied my logic. I programmed for years in a number of languages and I didn't run into something quite like this. Access is a different kind of animal, even in it's logic elements.

I want to thank you for telling me the correct logic states. I won't be able to check this out until Monday at work, but I sure am going to give it a try.

I would like to ask you one further question, if you can answer it. In my query, under Condition, what would you recommend being the conditional logic statement? All I can find so far is using the IiF conditional statement using the field name such as IiF("One_Is", -1) In other words, if my checkbox is TRUE (Yes Conditon) then filter for all records in the record set which meet this condition. I am not sure that I have written this correctly since I normally don't use SQL statements to do things. Do you have a recommendation?

Thanks again.
Jimc52

[PEB]

The construction of IIF is

IIF(Len("One_Is")>1, True, False)

In function of your regional Settings it can be also:

IIF(Len("One_Is")>1; True; False)

[NeoPa]

The negative 1 is not the extra here (that is the 1).
Logically -1 is used because it represents 1s in all bit positions and is quite standard.
MS invented the unset state (I think) which can actually be very handy.
I'm pretty sure, though, that controls that can hold boolean values have a property which allows / disallows the new value.
You should be able to disable this mode on your controls to give what you expected.

True / False :
Any non-zero value is treated as True in VBA. This does not mean that it is treated as =True!
When writing code it is sometimes important to check :
(YourValue) and NOT (YourValue)=True.