Reports

[eallen]

Hello. I'm working with data that is populated in some fields of the table and not others.When I created the report 3 column report, I used the "No data" in the property sheet to omit (make not visible) those blank fields in the report. That worked only to a point. Since my report has 3 columns ex. column a, b, c. If there was data in a field in column "a" and none in column 'b' or "c" there would be blank white spaces in the field in column 'b' and 'c' for those fields. Since there fields with data in both column 'b' and 'c', below the empty space, is there a way to have the data in the fields below the blank white space come up to fill that void and match up with the data housed in column 'a'? Essentially, eliminating any blank white spaces in the report where the fields are empty. Any suggestions will be greatly appreciated.
Thanks!

[aytee111]

Not really sure what you are asking. Can you provide a picture, and/or your database?

[eallen]

Aytee111, thanks so much for the response. As requested, I've attached a picture of the database. 080417_BlankFieldsIlustration-AssistanceRequested. jpeg.pdf

[aytee111]

Nice!

I'm not really sure, I would have to do some experimentation. Such as setting the CanGrow and CanShrink properties to yes, making the textboxes really narrow (vertically) and pushing them up closer to each other, removing the borders so that it is not so apparent that there is a line missing. So if you can post your database with some sample data I could try some of those things. Also, you try and see if any of these things improve the situation.

[aytee111]

See this thread: https://answers.microsoft.com/en-us/...1-c6ee0be8b36d

It mentions that CanGrow and CanShrink properties don't work properly when there are labels, which you have. They also provide a solution for that.

[eallen]

Aytee111, thanks so much for the insights, tried the CanGrow, and CanShrink properties and they worked. The only this is the label solution, I will keep trying now with ChristopherH2701's response from the link you provided. That might just do the trick! Many thanks!!!

[eallen]

Hi Aytee111,
I've worked on the database again and tried switching the database labels to text boxes since they won't work with the CanGrow and CanShrink properties. I used the code provided -
=IIf(IsNull([YourMemoFieldName]),Null,"Label Caption Value")or
=[YourMemoFieldName] +"Label Caption Value"
However, the label becomes an unbound field and still code provided doesn't work. As a result, the label which I would like to appear in the report when there's data in the associated text box, isn't visible when there's data. I also set the text box to CanShrink and was not successful.
I also tried using these tips - http://forums.techsoup.org/cs/community/f/20/t/10857.aspx Unfortunately, even using the event properties and visible options, from Steve Quigg's suggestion, the label in one of the columns didn't appear when there was data either. Do you have any ideas why? I look forward to your feedback. Thanks so much!

[aytee111]

Can you post your database? The report and sample data is enough. The link I found was for Access 97 so who knows what has changed. It would be easier for me to troubleshoot with the changes that you have already tried.

[eallen]

Hi Aytee111,
Absolutely, I tried tinkering with it a bit, and it still doesn't work. Hope it's better on your end. Thanks!

[aytee111]

Don't post databases that are in a bad state - this database doesn't even compile. We are not here to fix your problems for you.

It doesn't look like you did any of the instructions posted, either here or in the link I posted. I have showed you how to do it on the address, I am sure you can do the other fields that you need.

[eallen]

Hi Aytee111, thanks so much for your code MailingAddress field. I'm a novice and have never done VBA before and this was my first attempt at trying to make the Mailing Address Label not visible when the text field associated with the label MailingAddress was Null. I'm sorry for any inconvenience caused. I stumbled on this project and I'm jumping right in. Wish me luck!
I really appreciate your insights.I notice how, given your code, the MailingAddress label stays visible in the entire report sample and the ORGAddress Label is not visible at all. Mygoal was to do the reverse. Therefore, have the MailingAddress label visible only when there was a value in the associated text box. I attempted to do so by putting the code in the detail section of the report properties under the event tab and print option from the TechSoup instructions I posted previously. Somehow, the instructions didn't work for either of us. I had an unbound textbox and didn't put the correct Label Option Value in the code. Unfortunately, your code kept the MailingAddress visible permanently despite a null value in the associated text box.
I will continue to tinker around with the MailingAddress Label and see how to make it not visible as I originally intended when the MailingAddress text box is null. Again, thanks for your help!

[aytee111]

Yes, I only had one address label and removed the other, there was so little space - but now I see what you are saying.

There is no code involved with this. The "if" statement is put in the control source property of the textbox (label). You can use the one that I did for you and copy the structure and the intent and do that for all labels that you want to hide/unhide. This works for me perfectly, based on the instructions posted in the above link.

[eallen]

Hi Aytee111, thanks for your input. I'll work with it some more and get back in touch with you. I saw the IIf statement in the control source property and will use that as a guide for the remainder of the report. Yes, my colleague whom I am assisting wants all the information on the report. Hence the lack of space. I appreciate your patience.

[aytee111]

I was wondering why there was so much space between the address lines and have now checked and there is "padding" added to each textbox. In the properties of each text box you may want to remove it, set it to 0.

[eallen]

Aytee111,
Hi, just saw what you were referring to in the properties padding - thanks for the tip! I didn't realize what a difference the padding could make even in those small increments.