Nested If Statements?

[shoelesscraig]

Long story short, I have a table (let's call it "tblA") with records in it and each record has 25 fields that are checkboxes. Along with each checkbox is another field that is text. So, 50 total fields, 25 checkboxes, 25 short text fields.



I have a query (let's call it "qryA") that will show me every record from that table that has AT LEAST one checkbox "checked". Doesn't matter which one, could be any of them or all of them. This works fine.

My problem is....I'd like to create a report that shows me each record that has a checkbox "checked" (just like the "qryA"), but I would like it to show me the text from ONLY the fields that correspond to the boxes that are checked. Make sense?

So if I have:

Check1: TRUE
Text1: Red

Check2: FALSE
Text2: Blue

Check3: TRUE
Text3: Green

Check4: TRUE
Text4: Purple

The I would like the report to tell me for that record: Red, Green, and Purple. You can see how this gets crazy with 25 possible text fields. I COULD have (although unlikely) all 25 checks TRUE, so therefore there would be 25 text fields to display. I assume my report will need 25 fields and some kind of "If" statement that says (for the first one) that if #1 is checked display Text1, if not, then If #2 is checked, display Text2, etc.

I figured out how to do this with If statements for the Control Source for the first 10, but I guess you can't do more than 10 nested If statements...? How else could I do it? Thanks ahead of time.

[ItsMe]

So each one of your 25 textboxes represent a different color? Why not list all of the colors in a separate table? Replace all 25 columns in tblA with a single column of type Long Integer and use this column as a Foreign Key field to the Primary Key field for your new table of Colors.

[shoelesscraig]

No, I used colors as a quick example here on the forum, the "text" that I'm referring to can be a couple of sentences long, completely different every single time.

[ItsMe]

No, I used colors as a quick example here on the forum, the "text" that I'm referring to can be a couple of sentences long, completely different every single time.

In that case you would need to change each of the 25 Yes/No fields in tblA to Long Integer and create another 25 tables. The additional 25 tables would represent 25 different catagories/entities.

Perhaps it would be beneficial to take a step back and provide an explanation of the business logic. As it is explained in post #1, there does not seem to be a possible solution. You would be hardcoding variables.

[shoelesscraig]

Basically, its a "To-Do" list for me on the projects I'm working on. (Included in tblA is another 15+ fields that contain all kind of information about each project I work on, I just didn't mention them before b/c they really weren't relevant).

So when I'm working on ProjectA, when I have something I need to do on it, I check box 1 and type in what needs to be done in the text box for 1. Same for 2-25. As I finish each task, I uncheck the box for that task. Could be finished in any order, not necessarily stating with "task 1".

I wanted to be able to run a report each morning for myself that basically said "this is what is left to do on all your projects". I only wanted to see tasks that ARE NOT finished, since the ones that are don't really concern me any more.

[shoelesscraig]

I used the number 25 b/c by the time the project is started and finished, I shouldn't have more than 25 "to-do" items for it. For some it may be the full 25, but for some I may only need to be reminded to do 2 or 3 things, so the other 22-23 boxes will never be used for that record.

[shoelesscraig]

I have gone into design mode, made sure that "Has Module" is YES for this report. So, I can now see things in VBA. Does this help? If so, I'm not sure how to write the code or WHERE the code should be (Report>OnLoad? Report>Open? Etc.).

[ItsMe]

I would recommend what is suggested in post #2. Or maybe do the opposite, kinda. The opposite meaning, store the primary Key of tblA in the new, second table. It depends on which side has the "Many" side.

If you are trying to assign a specific task to a project, you want to define the task in a separate table as a single record. Use the Unique Identifier/Key from that record and use that value, the Key Value in a foreign Key field in another table.

In the end, you may discover that you need several tables. One question you might want to ask yourself is, "Will the description of these tasks change often?". This will help when determining the relationship of your entities.

I suggest doing a little research on Relational Database. Some key words are, RDBMS, Normalization, Primary Key, Foreign Key, JOIN. Here is a short video that might introduce you to the idea of creating an Entity Relationship Diagram. You will need an ERD in order to proceed.
https://www.youtube.com/watch?v=-fQ-bRllhXc