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

Need a report checkbox which shows a check from any of 3 table fields

P: 10
I have three check boxes in a table which I would like tracked in a report check box showing a check mark if any one of the three fields has a positive value. For arguments sake the three fields in the table can be called: check1, check2 and check3. The report check box will only be clear (no check) if all 3 table check boxes are empty or clear. Is it possible to have this within a report? If not, how can it be handled so that it displays within a report and what logical code is required in the tracking check box?
Apr 21 '12 #1

✓ answered by NeoPa

Your question is so ambiguous that an appropriate answer is hard to find.

If you're dealing with boolean fields (True or False values only) then :
Expand|Select|Wrap|Line Numbers
  1. SELECT ([Check1] AND [Check2] AND [Check3]) AS [ReportCheck]
If you are actually referring to numeric fields as part of your question implies, and you really want to check for positive numbers rather than simply checking for True values, then :
Expand|Select|Wrap|Line Numbers
  1. SELECT (([Check1] > 0) AND ([Check2] > 0) AND ([Check3] > 0)) AS [ReportCheck]
Please try to make your questions make clearer sense in future.

Share this Question
Share on Google+
10 Replies


nico5038
Expert 2.5K+
P: 3,072
Just add a column to a query based on the table like:
Expand|Select|Wrap|Line Numbers
  1. select field1, field2, IIF(check1=true or check2=true or check3= true,true,false)
  2.  
And use that for the report.
Getting the idea?

Nic;o)
Apr 21 '12 #2

NeoPa
Expert Mod 15k+
P: 31,418
Your question is so ambiguous that an appropriate answer is hard to find.

If you're dealing with boolean fields (True or False values only) then :
Expand|Select|Wrap|Line Numbers
  1. SELECT ([Check1] AND [Check2] AND [Check3]) AS [ReportCheck]
If you are actually referring to numeric fields as part of your question implies, and you really want to check for positive numbers rather than simply checking for True values, then :
Expand|Select|Wrap|Line Numbers
  1. SELECT (([Check1] > 0) AND ([Check2] > 0) AND ([Check3] > 0)) AS [ReportCheck]
Please try to make your questions make clearer sense in future.
Apr 21 '12 #3

P: 10
Sorry, here is an elaboration on the particulars of my request.

1. I have a table, called DRM
2. In the table are three checkbox fields called EV1, EV2 and EV3
3. I need to show whether any of the three are true in a checkbox field in a report as space is limited to print horizontally and I only need to see if any one of the three are true or none are true, meaning false (yes = any one field is checked, or no = none of the fields are checked)
4. Is there a way to show a new check box field which checks the other three fields in the table and returns a yes or no (checked or not) depending on the values of the table fields as described above?
Apr 23 '12 #4

Rabbit
Expert Mod 10K+
P: 12,357
You can use NeoPa's first method, just change the ANDs to ORs.
Apr 23 '12 #5

NeoPa
Expert Mod 15k+
P: 31,418
Rabbit is right on the money. To illustrate, and include the information you have included now (Good move BTW. Better in the first post generally, but learning is always good) :
Expand|Select|Wrap|Line Numbers
  1. SELECT ([EV1] OR [EV2] OR [EV3]) AS [AnyEV]
  2. FROM   [DRM]
Apr 23 '12 #6

P: 10
I have the code which works given the situation I have in the table as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT IIf(PCBanner='Yes' Or PCColumn='Yes' Or PCColumn='Yes' Or PCFootprints='Yes' Or PCPoster='Yes' Or PCOther='Yes','Yes','No') AS PC
  2. FROM CRF;
This works as a query, however, I have been unsuccessful in having this code work as the source for a new field within the report.

Excuse my ignorance; what is the correct place for this code to work as the source of a new field in the report?

Or... can the query be the source of a field in the report?

Almost there...
Apr 24 '12 #7

NeoPa
Expert Mod 15k+
P: 31,418
Now you're confusing me. This is not consistent with your original question, so it's hard to know which information is accurate.

If this latest post is accurate then you are dealing with Text values and not Boolean ones as would be expected when using a CheckBox control. You also asked originally for code to check if the values were positive. That's a question that makes little sense when dealing with Booleans, but assuming your grasp of Booleans is slim, as most people's are, then understandable. If the values are 'Yes' or 'No' (Text values) then I can't see how anyone could ask such a question. It's beyond meaningless. Hence, with all this info to go on, the only conclusion I can reach is that you don't seem to know what you're dealing with. Not an easy starting position when setting about trying to answer a question.

For now though, if you have a working query then I'll leave that with you.

Lucrecious:
can the query be the source of a field in the report?
This bit makes sense at least. Yes. You can create a field in your query, just as you have here, and use that field in your report.
Apr 24 '12 #8

P: 10
Thanks NeoPa, and so how does one include the field from the query in a report? The query does work, thank you for the sample code which I based the query upon in order to get it to work, however, I don't know how to create the field with the query results as the source.
Apr 24 '12 #9

NeoPa
Expert Mod 15k+
P: 31,418
That's just confusing. Is your report bound to the query? Obviously it must be for this to make sense, yet from your question it seems maybe it isn't.

If it is, then it's simply like using any other of the fields of the query. If it isn't then you may need to look at this again from scratch, and maybe learn some of the very basics before trying to use Access.
Apr 25 '12 #10

P: 10
NeoPa, I figured it out, and, thank you.
Apr 25 '12 #11

Post your reply

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