By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 938 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

Check Boxes + Sort by Condition in Access Report

P: 3
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
Nov 17 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 18 '06 #2

P: 3
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
Nov 18 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 18 '06 #4

PEB
Expert 100+
P: 1,418
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)
Nov 18 '06 #5

NeoPa
Expert Mod 15k+
P: 31,485
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.
Nov 18 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.