Skip Blank Fields in report to compress column report

[Tfrank]

I have a report where the fields in the detailed section are placed vertical on top of each other (The fields are very long). In the detailed section I have 5 different fields, occasioinaly some fields are blank. In order to minimize blank areas in the report, I would like to be able to compress the report to avoid blank areas in the report. Does anybody have any suggested methods of how to do this, including VB.

Thanks in advance for the help.
Tfrank

 

[LarryDeLaruelle]

TFrank:

Won't swear to it, but I believe if you set the field's Can Shrink property to Yes, it will compress as you want.

hth

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Papermaker]

The shrink property works fine to prevent null fields from showing in the report, but what about the null field's Labels?

 

[dhookom]

Papermaker,
You can change the field's label to a text box. For instance, if you have a label with a caption of "First Name" used for a text box bound to [FirstName]
-change the label to a text box
-set its control source property to:
="First Name " + [FirstName]
-set the text box width to only show "First Name"
-set it to Not Grow but allow Can Shrink
If [FirstName] is null then the "label/textbox" will shrink.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Papermaker]

Thanks Dehookom,
I'm new at this stuff. Just doing my internship now in database. This is my first one...I didn't think of that method...hehehe
Thanks Again...Papermaker

 

[Papermaker]

Well, it did not work. Here is what i have:
(Text box bound to)
E1_user
(Label text box control source)
="Email 1 username "+[E1_user]

I set the label text box to shrink, not grow.
I also set the bound text to shrink, not grow.

I get the first part of the text box label, Email 1 on the form, but everything else is shrunk. If shrink isin't working then why do I get part of the label not all? Tried a couple of changes in syntax, but no go.....Papermaker

 

[Papermaker]

Nevermind about the last part. I set it to grow and got all the label text. So I guess its just is not shrinking properly.

 

[dhookom]

If you have a text box with a control source like:
="Email 1 username " + [PossiblyNullField]
then, when the [PossiblyNullField] is null then the entire contents of the text box will be null. If, however the PossiblyNullField is a space or zero-length-string then it will not dissappear.

You don't want this box to grow because you don't want to show the PossiblyNullField value.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Papermaker]

First of all thank you for your patience. I think I understand what your saying, so to clarify let me tell you some background.
First off I am working on a new database (no records in this copy. The E1_user column is datatype text so no 0's in the field as a default value. I could be wrong but because the database is void of records as of yet I should get nothing on the report yet right? Or am I not on the same page as your explaination?

 

[Papermaker]

I forgot to metion the first time....the shrink did work on the bound text box just not on the first part of the label...

 

[dhookom]

Put some records in the table. I am not sure how you can expect to test a feature without having some records in the table. Make sure your test data has at least one record where the E1_user column is Null.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Papermaker]

I already did that but I did it again just to confirm. Here is what I got.
The bound box grew to show the fields with records ie; the user names and the user passwords.

control source properties for label boxes are:
="Email 1 username "+[E1_user]
="Email 1 password "+[E1_pass]
etc: up to 10 email usernames & passwords.

I have 3 email usernames & passwords in the record out of a possible 10 as I said.
The emails that are null work as you say they should, there gone, but the three that grew because there was data, only show half of the label ie: Email 1 instead of Email 1 username. Do I need more spaces in the source code property box for those labels to show all, or is it an alignment of the text boxes in the report thats the issue?

 

[Papermaker]

Thanks dhookom,
I got it. It was a text box allignment problem. I did not have them long enough to include the code and the Email 1 username.
Thanks again

 

[dhookom]

If you are a student creating your first database, one of the first things you should understand is that Access is a relational database. Having repeating groups of fields like [Ex_user] is not normalized. Search the web on normalization to find out more information.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Papermaker]

I'm not sure I follow you. These fields hold different information for each username and password. However there are several different packages, each having a different amount of email accounts. The package could have as few as 3 and as many as 15. This is a database for a reseller of web hosting packages. Each record is for a package, and a customer can have more than one package associated with his ID. Besides all that, data redundancy is the least of my problems considering the amount of customers the database tracks, about 15 right now. This thing is kicking my arse right now because she wants everything on tabs. I'm having a big problem trying to get these forms to link properly to the main form. Also, this is an office database with no interaction with the web. She plans to try to use MySQL and PHP on a unix web based server for her internet database. I am open to suggestion as always, thats how I'm sure you got as good as you are. I'm just trying to prove you can teach an old dog new tricks...hehe

 

[dhookom]

Ok, if you have up to 15 pairs of fields for each customer then you have an extra 30 controls on forms and reports. If you want to search for a user name, then you would have to search in all 15 fields. When you find the proper username, you will have to then reference the pwd for this user.
I would set up a table related to the Customer table that would contain one record for each of the related email user names and passwords. This allows any number of user names, easy searching, fewer controls on forms, much more flexibility,...


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Papermaker]

There will be no searching for usernames as all the information is set up in the tabs, all realted to the customerID. Each tab contains different information about the customer and is able to be edited in that one spot. You can edit, add or delete from any or all information contained within these tabs. Now I don't know if that would be better or not, but this is only an internship without pay. I had a hard enough time getting the thing to work right in the first place. Also, I only have about 3 days left before I'm looking for a job, and I just got the thing working properly an hour ago. I do want to thank you for your help though. I did get the report working, just not exactly as she would like. I am working on it though. She wants a mail merge type of report, but she wants a one button directly to HTML type of setup so she can email it. I don't know VB so I can't see how to do a one button setup for that in access. I see how to export to HTML but as you know it doesn't retain the format during export....I'll talk to ya later...Papermaker