Convert database values to check boxes in report

[Joseppi4ever]

I have a database field called "report_type" that has three possible values (Initial / Follow-up / Final) selected via drop down box.

I need to convert that selected value into a checked box on a report.

[zmbd]

Basic would be:

IIF(testcondition,-1,IIF(testconditon2,0,null))

Assuming that the check box has the triple state set.

A little more detail is required.

[twinnyfo]

You could also use an option group, which would display a check in a particular box, based on the value of the field. But, again, as Z says, we need more details.

[zmbd]

Ok, back from the lab-timer
It would actually be easier from a form design if the value of the bound field was either 0, -1, empty/null for the checkbox and the check box triple-state set to true. (so you get: true(-1) = [x], False = [ ] and null = [O])

For the option group the requirement here is for the field to return of 1, 2, 3... to toggle the proper button

In either case, I'd build a query with a calculated field that returned the proper value and bind that field to the control.

[Joseppi4ever]

OK - back to this old problem.

I now have an option group called Category created on my report which lists 5 separate choices (Check-boxes). I need one of the five check-boxes selected based on the field (Category) contained in my tblIncidents.

How do I link the tblIncidents.[Category] field to this option group on my report so that the correct Category is selected when the report is printed.

[NeoPa]

If you have an optionGroup then you should know that, as long as each item in the group having a value TRUE or FALSE, and only one of them being TRUE, the OptionGroup also has a numeric value that reflects which item is selected.

So you can use the value of the OptionGroup to determine which category is selected.

[Joseppi4ever]

I understand the concept but lack the practical experience to make it happen.

How do I make the option group in the report display the data contained in the database?

[NeoPa]

Well, a million different ways. To help you display it the way you want it displayed i'd need to know how you want it displayed. You haven't included that information in your question yet.

If you include some example data then your lack of expressing the question properly can probably be less critical.

[Joseppi4ever]

OK. I've attached a small subset of the database I am trying to sort out. The report "INCIDENT REPORT" is based on QUERY "qryISSIH"

When you launch the report, you will be asked to enter an Case Number.

Use 17-0056, 17-0058, or 17-0059.

Ignore the next request for a Report Type (Just hit OK)

The report will populate and then you will see the Option Group on Page 2 of the report

The options are based on the "Category" field in the qryISSIH.

Which box shows as selected should be evident based on the "category" field.

I am forced to used this particular report format as it is mandated by our headquarters.

Once completed, we print it to PDF and then email it to our central repository for further investigation (as needed) and/or filing.

Your assistance is greatly appreciated.

[NeoPa]

So, if I interpret your directions correctly then I would assume you're looking for the data to be displayed in the report as a list of possible categories where the actual category has an X against it in a CheckBox. Is that right?

Bear in mind this is the explanation I was looking for. Very few people will download an attachment.

1. It takes a lot more of their time in order to help you than is necessary if you ask the question properly.
2. Access databases have the possibility for executable code which can be malicious.

I get the feeling you're doing your best and hadn't considered those aspects so I've cut you a break this time. Please remember though for future responses.

This is what I see at the top of page #2 and that's what I assume you're referring to.

For now, just assuming everything I've said above is actually what you're looking for, let me ask have you tried an OptionGroup in the report itself? It seems perfect for what I think you're asking for.

[Joseppi4ever]

I appreciate the help and Yes, you have deduced my wishes correctly.

What you see in the report is my attempt at putting the option group in the report but I don't know how to link it to the "Category" filed in the query (qryISSIH) and have it display the correct category in the option group.

[NeoPa]

Looking further into your database it seems you have the full text as the index into your [tblCategory] table. I won't go into all the reasons why that isn't the recommended way of doing things but if you can build an OptionGroup that expects the simbple values 1 - 5 and populate your query with data that converts from your category full name into that value, then things may work for you. I suspect the quickest and easiest way to see this work (Though this wouldn't be my recommended design, you understand.) would be to add an extra numeric field into [tblCategory] and return that linked value in your query. Maybe as an extra field to make it clearer to anyone working with it what it refers to.

PS. I posted this before seeing your latest post but it seems to be what you need anyway. Let us know if this does it for you or if you need further help on this specific issue (Other issues would require to be posted separately of course).

[NeoPa]

Bear in mind, if we can get this working for you in the next couple of days then that's less than 2.5 years from start to finish. Possibly not our quickest ever success :-D

[Joseppi4ever]

Obviously, I am not a DBA nor a developer of any kind. If I were to add the extra field into the tblCategory so that it showed the simple values 1 thru 5 and reworked the query so it showed those values, how would I make the option group show the correct checked box?

[NeoPa]

We'll take one step at a time for now, but I don't think you'd need to do anything further there. That part seems perfect to me already. The only problem earlier was that you were feeding it a string value instead of a numeric one.

If you look at the Control Source of the OptionGroup control, then at the various Option Values of the associated CheckBoxes then imagine if the value of the OptionGroup control matches any one of the Option Values of the associated CheckBoxes then you'll see that the matching CheckBox will be shown with the X.

Obviously, if the new value (1-5) were added as a different field instead of just [Category] as it is currently, then you'd need to change the Control Source of the OptionGroup control to match the new field name.

[Joseppi4ever]

I know you are trying to point me in the right direction to a realization but I am just not getting what I need to do here.

I know I need to make the category field seem like a numerical value of 1 to 5 so that the right box shows as selected but I am not getting how to take the string currently in the category field and make it that value.

Is it an IIF or CASE statement that has to be written?

[NeoPa]

No. In your query add [tblCategory] and link from your [tblIncidents].[Category] text field to the PK ([tblCategory].[Category]) and then drop the newly created numeric field into the query.

[NeoPa]

It should look something like :

BTW It works fine my end. A tick rather than an X it seems, but who's counting ;-)

[Joseppi4ever]

And now I would change the Control Source of the Option Group to CATNo instead of Category?

[Joseppi4ever]

You ROCK!!

Thank You!

[NeoPa]

I'm guessing you know the answer to that by now, but yes. Exactly.

BTW I'll change the BestAnswer post to one that is more helpful to others reading the thread. Thank you for selecting it though. They all help for my re-nomination for Access MVP.