triple state checkbox on report help

[kc112]

First, I have been reading about this triple state checkbox a lot (which there is limited information) and apparently there is a large debate as to whether or not it should exist or if users should even incorporate it. So if someone has a better idea, I'm open to it!

I originally set my fields up as regular checkboxes with a YES/NO value. Yes = positive test result, No = negative test result.

Then on my report, I used a simple iif([field] = true,"Patient positive","Patient was negative"). Which worked great!

Now, the problem...I've been told I need a third option! DID NOT PERFORM. THis is where it gets fishy. I opted for changing my yes/no to an integer format, updated my property of triplestate on the form to YES, and it works great on the table and the form. It correctly switches from YES, NO, and NULL(did not perform).

But, on my report, I cannot get all of my text to populate. I cannot use the iif function anymore (sinces its not a boolean field) and I tried the format property to: "";"Positive";"negative";"did not perform" which in essence works. But when the patient is positive it actually has to state: "";"The patient tested positive for the anterior drawer sign of the left ankle. An increase in pain implicates the anterior talofibular ligament may have a possible sprain.";"The patient tested negative for the anterior drawer sign of the left ankle.";"" and when I enter ALL of this into the format property, I get cut off! Its too big!

How do I work around this?

How do I get all of my text on the report?

Any and ALL ideas are welcome and appreciated!!

Thank you.

 

[dhookom]

You have found why I typically avoid using yes/no fields. If this was my application, I would create a small lookup table of values:
[tt]

-1 Positive "The patient tested positive for the ..."
0 Negative
2 Did Not Perform
[/tt]
You can then add this table to your report's record source and join the fields.

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

Can I still use a checkbox with that?

If yes, I am confused as to how to get it to print on the report. I am assuming something like:

if fieldname = 1 then "Patient tested positive..."
if fieldname = 0 then "patient tested negative..."

is this correct?

 

[dhookom]

I would use a combo box on the form rather than a check box. The report record source should contain the table with the values "Patient..."

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

If at all possible, I would like to avoid the use of comboboxes here. There are over 30 tests and 30 combo boxes means a lot of room on one form and 30 table references.

Is there any way to reference specific text in a textbox to trigger different text.

I was hoping....if I set the triple state check box, create a textbox on my report with: "";"positive";"negative";"didnotperform" in the format property and make it visible = false.

Then write some function that:

if txt1 = "positive" then txt2 = "The patient showed positive blah blah blah....."

if txt1 = "negative" then txt2 = "THe patient showed negative....."

Is there any way to acomplish this?

 

[dhookom]

If this was my application, I would normalize the table structure. IMO 30 tests means 30 records not 30 fields. In my design, a new test or two would mean a new record or two in a table. In your current design it means new fields, controls, expressions, code, etc.



Duane
Hook'D on Access
MS Access MVP

 

[kc112]

Yes, I knew that answer of normalization was coming, but I do not think that it would be user friendly. If I added a table with 3 fields

pktest - autonumber
fkpatient - number
selecttest -combobox with test options
selectresult - combobox with positive, negative, didnotperform

You are correct that the user could select a test and select the result in a continous form. But that would be a drop down box with 30 options and the user would have to use a drop down box with 30 selections 30 times. I'm not sure the users of this database would appreciate that at all!

I was perusing around and was hoping that some sort of Case function could help me here.

Like

Select case
if field = 0
then "positive........."
if field = -1
then "negative........."
end if


but I have no experience with case functions and I am not sure if they are usable with textboxes to populate text on a report, as I am hoping they are.

Is this possible?

Or, do I have a misconception about normalizing?

Or, maybe I need to repost this question as a case function question instead!

thanks for your help,
Kristyn

 

[dhookom]

You might want to look at At Your Survey which automatically appends all 30 test (actually questions in a survey). I have created similar solutions for other types of tests.

Do all 30 test have similar or the same results for null, -1, and 0 or are they different. I would probably build a small user-defined function that accepts the value and returns the appropriate text. When your text changes, you can simply change the code. This would be even better if you had a small table of lookup values containint the text as I suggested on 29 Jul 11 14:00. Data belongs in your tables, not your code.

Duane
Hook'D on Access
MS Access MVP