Omit empty fields on my report???

[kc112]

Hello,

I am new here and new to using Access, so please be patient...

I am creating an program to print and track treament outcomes at my office. I made the table and then created a main form that outlines data such as patient name, complaint, ID #, etc.. THen in that same form there are (2) subforms; 1 for cervical results and 1 for lumbar results.

Since this will be used by basic data entry personnel, I need to make it as simple as possible. So directly on the form, I added a 'Print' command button that directly prints a report of the current record.

The problem is that on the print command function for the single record from the form, I only want to print the data that is relevant. i.e. not all patients have cervical and lumbar complaints. I only want the cervical to print if there is data entered and same with lumbar. Currently, it just prints the blank fields on both the cervical and lumbar subforms.

How and where do I input the criteria to ONLY print the fields from the subform with data entered? So, i only want to either print the cervical subform or the lumbar subform, not both.

Please and thank you!!!
Kristyn

 

[Ray1127]

Based on your statements it sounds like you are printing the forms not a report. If it is actually a report you could go to the report section where those fields are located and enter the following code

If isnull(field1) Then textfield1.visible = False Else textfield1.visible = true

However if you are simply printing the form and not a report that will not work.

 

[lameid]

kc112,

Ray sounds right that you are printing the form and not a report.

I assume that since you have multiple subforms and you have a patient to results setup that you can have more than one result per patient for each type of test. If you need help creating an Access report, more information about your data structure and whether you want to see all or particular reuslts on the report will help. It sounds like you have a table for each type of test which may or may not be best... question, are the two tests similar enough they can go in the same table? In other words, can there just be an attribute for test type?

Ray's solution for hinding the field on the report will work but may be a little difficult for someone to jump into for the first time.

In a report, if you don't have a value, setting the can shrink property of the control should be enough to shrink it down. If you want to 'hide' your 'labels' as well, know that textboxes work just as well as labels. The difference is a textbox can take a control source expression. Because you want the resultant value of an expression and not a field, you need to use the equals sign to start. Then vary similar to Rays suggestion you make an expression to conditionally display the label...

=IIF(Isnull(),"","Label Text")

So in short, Ray's solution is fewer steps but mine may be more intuitive.

 

[dhookom]

Kristyn,
Can you provide significant table and field names? I fear your table structure isn't normalized but I can't tell from your description.

You can set text box controls' Can Shrink property to Yes to hide them and possibly close up blank spaces in reports. If you have associated labels, I typically convert these to text boxes and set their Control Sources to something like:

="Your Label Text Here   " + [YourFieldNameHere]

Set this text box only wide enough to display the label text and set its Can Shrink to Yes.

Duane
Hook'D on Access
MS Access MVP

 

[kc112]

Thank you so much for your prompt responses!!

Yes, it is defintely a report that I am printing from the form. I can post the command sentence I am using tomorrow when I am at work.

Ray, I would like to try your solution of using " If isnull(field1) Then textfield1.visible = False Else textfield1.visible = true" However, I am slightly confused as to where to enter this command. I open my report in design view, but where do I enter the command? Do I highlight the field box and build event, open code builder, and then type the command?


Lameid, the two test result are very different. The patients name, ID, HPI, location, severity, etc... are all the same and thus I have them on (1) main form. But then, depending on the complaint, the patient either had a cervical exam or a lumbarexam. The results of the cervical and lumbar are drastically different and each has about 15 fields that are different. For example, a cervical has a field for HEAD TILT: then i have a drop down box with : LEFT, RIGHT, SLIGHT LEFT, SLIGHT RIGHT, FORWARD, etc.. but Lumbar exam talks about HIP PLACEMENT. so you can see they are very different.

I would like to try your solution as well. When I change my labels to text boxes, then I open my report in design view, highlight the text box, build event, code builder and type the "=IIF(Isnull(),"","Label Text")?? Also, under the properties tab, I choose CANGROW NO and CAN SHRINK to YES. for each field box.


dhookom, I have no idea if my table is normalized! I have a table called "Enter Treatment Plan Data" I also have tables associated with this main table for my drop down boxes, for example, I have a field called Diagnosis Codes, with a drop down and associted table called DIAGNOSIS, that lists all the available diagnosis codes.

I created a main form for ENTER TREATMENT PLAN DATA, and then added a command button that opens a subform, where I added the fields for either cervical or lumbar exam results. What information are you looking for? maybe tomorrow I can post a sample database.

Question! What is your opinion on this...for my subforms, since the information needs to be associated with the same patient ID and treatment plan date, is it better to add the cervical and lumbar fields to my main table or create 2 additional tables (1 cervical and 1 lumbar) and give them a parent/child relationship and connect them with a primary key to connect all 3 tables?

 

[dhookom]

I contracted at a hospital about 10 years ago and one project was a patient back and knee survey. You can find the results by searching "hookom at your survey". It can be found at

http://www.rogersaccesslibrary.com.

This demo shows what I mean by normalized.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

To clarify the 'code' I gave you should be the control source which is also a property of a text box.

I was concerned like Duane that your data may not be normalized which has to do with table structures. You should be able to find a number of descriptions for it on the internet by searching for "data normalization". They are a set of rules to ensure 'proper' data structure. My rule of thumb is that if you have not done the first three, you have done something wrong. There are reasons to normalize completely or incompletely. They have to do with the usability and processing the data.

In general I would say your Main form Sub form setup is correct for data related as parent/child as you have it (it is a correct technique). On the surface it sounds to me like your data may be normalized (enough). However, I would take Duane up on his offer to look at his system as he clearly has experience working with this type of data I do not.