I am running Access 2007 and have a report pulling data from a query in which a record may have several fields that are left empty. I need the report to display only fields that contain data and leave off the empty fields and labels that go with them. All lables beneath would also have to be pused up so that the excluded lables and fields would not leave a blank spot on the report. I hope I explained this well enough. I am not that skilled in Access and am having problems. Thanks for the help!
Report to exclude null values
- Thread starter bday
- Start date
HiTechCoach
Well-known member
- Local time
- Today, 09:57
- Joined
- Mar 6, 2006
- Messages
- 4,357
Are you using sub report for related tables?
Have you tried setting the can Shrink property to No?
It sounds like you want the layout of the report to change for each record, based on the null/not-null status of certain fields.
Sorry, I don't know a good way to do this, but the quick-n-dirty way is to have a text box with it's Control Source property set to
=[field1] & " " & [field2] & " " & [field3]
If field2 is null then the output will look like
field1 field3
Sorry, I don't know a good way to do this, but the quick-n-dirty way is to have a text box with it's Control Source property set to
=[field1] & " " & [field2] & " " & [field3]
If field2 is null then the output will look like
field1 field3
Mr Smin's idea of using one long textbox which concatenates the field's would work with a vbTab or Chr(9) in between but you will have difficulty with two things: aligning the labels' caption using the same method and can grow. You can begin to see that this isn't going to be a easy unless HiTechCoach has some tricks up his sleeve
Hiding the controls isn't much of an issue it's the aligning of the controls that is the difficult bit. But here's the idea:
1. In the tag property of the controls (i.e. textboxes and combo boxes only), write a number that indicates their position in sequence. So the first textbox will be 1 and the last 12 (for example, if 12 was the max number of controls on the report). You don't need to worry about the labels as long as they are attached to the control.
2. On the Print event of that section (which I presume is the Detail section), run code to loop through all the controls (i.e. textboxes and combo boxes only) and hide them if they are Null or contain a zero length string.
3. Whilst looping through the controls, you save the tag property and the name of the visible controls into a multi-dimensional array or use something like a Collection or Dictionary object if you're familiar with these.
4. Based on what is in the array/collection, you can now check the Left + Width of the previous visible control and move the current control in the array to that position. Set a left margin of say 0.08cm of each control in the property sheet.
This is how I would do it and everything should align nicely if:
Hiding the controls isn't much of an issue it's the aligning of the controls that is the difficult bit. But here's the idea:
1. In the tag property of the controls (i.e. textboxes and combo boxes only), write a number that indicates their position in sequence. So the first textbox will be 1 and the last 12 (for example, if 12 was the max number of controls on the report). You don't need to worry about the labels as long as they are attached to the control.
2. On the Print event of that section (which I presume is the Detail section), run code to loop through all the controls (i.e. textboxes and combo boxes only) and hide them if they are Null or contain a zero length string.
3. Whilst looping through the controls, you save the tag property and the name of the visible controls into a multi-dimensional array or use something like a Collection or Dictionary object if you're familiar with these.
4. Based on what is in the array/collection, you can now check the Left + Width of the previous visible control and move the current control in the array to that position. Set a left margin of say 0.08cm of each control in the property sheet.
This is how I would do it and everything should align nicely if:
- 1. The Tab Order of that section is set appropriately and ...
- 2. the field has no data at all throughout the recordset.
Ok, all three of you lost me. 
I do not do this on a daily bassis so im not that skilled in Access. I do have an Access 2007 book "The Missing Manual" but I do not see the answer I need. Microsoft should have added a control labeled "hide field and label if its empty and move everything below up so the form is nice and neat". Im stuck.
I do not do this on a daily bassis so im not that skilled in Access. I do have an Access 2007 book "The Missing Manual" but I do not see the answer I need. Microsoft should have added a control labeled "hide field and label if its empty and move everything below up so the form is nice and neat". Im stuck.Hang on, are you talking about hiding blank records then?Ok, all three of you lost me.
OK, I partially got it. The "can shrink" property set to yes wasen't working due to the control layout. Once I removed control layout then the empty text box dissapeared in the print preview. I then deleted the label and created another text box and binded it to an expression:
=IIf(Trim(nz([Education_State1],""))="","","Education_State1:")
Now, if the text box "Education_State1" is empty then it is not shown in the print preview and the label (text box) is not shown either. However, I am now left with a blank spot on the report where the fields used to be. How can I tell it to move the remaining fields up to fill in the blank spot?
=IIf(Trim(nz([Education_State1],""))="","","Education_State1:")
Now, if the text box "Education_State1" is empty then it is not shown in the print preview and the label (text box) is not shown either. However, I am now left with a blank spot on the report where the fields used to be. How can I tell it to move the remaining fields up to fill in the blank spot?
I am trying to hide lables and text boxes if there is no data entered to be shown. I have sucessfully done this, but my report now shows a gap or hole where the label and text box would be if it were visible. Is there a way to get rid of the hole?
I believe the answer is that I want hide individual controls when that field has no data. Lets say a form has 3 labels and text boxes:
Name
University
Graduation date
I want the report to look like:
Name: John Doe
University: Lamar University
Graduation date: 8/1/2006
If there is no data in the University field then I want it to look like:
Name: John Doe
Graduation date: 8/1/2006
and not:
Name: John Doe
Graduation date: 8/1/2006
Name
University
Graduation date
I want the report to look like:
Name: John Doe
University: Lamar University
Graduation date: 8/1/2006
If there is no data in the University field then I want it to look like:
Name: John Doe
Graduation date: 8/1/2006
and not:
Name: John Doe
Graduation date: 8/1/2006
REIGNMANIAC
New member
- Local time
- Today, 09:57
- Joined
- Jun 22, 2011
- Messages
- 3
I am having the same issue; bday did you ever get an answer to your question?
HiTechCoach
Well-known member
- Local time
- Today, 09:57
- Joined
- Mar 6, 2006
- Messages
- 4,357
