omitting black fields from a report

[BettyWho]

Hiding blank fields from a report

Hi I had a look through the forums and could find a suitable answer to match what I'm looking for. I'm a bit stuck so I thought I'd throw it out there for HELP!

I've created a report based on emergency contacts however some fields are blank as there isn't a "contact" and rather then have lots of empty fields all over the report is there anyway to omit them based on whether they are populated or not? there might be 2-3 records that have "every" field filled the rest are varied as to how many contacts they have.

If needed I will post a sample.

Thanks in advance!

 

[Mihail]

If needed I will post a sample.

Good idea. Convert to Access 2003 or 2007. ZIP it before upload.

From your description I think that your DB is not properly normalized.

 

[BettyWho]

Hi I've taken two screen shots as I am unable to post a sample of the db due to the nature of the contents. However my normalisaiton is correct. The report I am trying to generate comes from a "master" table if you like there is no doubling up of information and the table is referenced by several other queries and a couple of tables so to not have superfulous information. That is neither here nor there though. As you can see from the screen shots I am trying to have the lines where there is no information not visible in the report. Every record has the same fields but only some records use every field. I have tried several variants of Is Null, Is Not Null, using the Can Shrink/Grow properties. and I just dont know what I've done if you are able to point me in the direction of some code or commands that would be relevant I would be grateful.

 

[JHB]

One way to do it is using sub reports for each section.
Sections are: "Principal", "Deputy Principal" etc.

 

[GinaWhipp]

As I see it...

Your Table set up is preventing *shrinkage*. If you set up your Table differently then you could use somthing like this...

http://www.access-diva.com/f4.html

...(which can also be applied to reports) and exclude the ones that are blank. This would allow your section to shrink.

The way you have done it cannot shrink because you have columns instead of rows. I would change my tables to be...

tblSchools
sSchoolID (PK)
etc...

tblSchoolContacts
scSchoolContactID (PK)
scSchoolID (FK to tblSchools)
scContactTypeID (FK to tblContactTypes)
etc...

tblContactTypes
ctContactTypeID (PK)
ctContactTypes (ie: Principal, Deputy Principal, etc...)
etc...

Then when running your report, you simply exclude the ones that have no number.

 

[BettyWho]

I can't change the structure of the information because of the sheer enormity of it. Each school has one line in a table and the columns consist of principal, deputy, deputy 2, deupty 3, deputy 4, registrar, registrar 2, gardener, and then the address details.

However only some schools have more then two deputies. here inlies the issue the can shrink can grow wont work obviously but I was hoping there was VB code that would help me exclude fields if they were blank on a certain record? I can't exclude them permanently because 2 or three schools have those positions filled. and the way you have suggested about wont work either because this report gives "all" the schools listed not just one. There are 250+ schools in the sample I am looking at currently that I am trying to get the report working for. Once I've got it sorted it needs to go into a bigger database. I hope that makes more sense. If anyone can help please let me know

 

[GinaWhipp]

Well, since the table is set up more like a spreadsheet... The only other solution I can think of is a host of UNION queries which would impact performance as you would need quite a few of them.

 

[BettyWho]

Re: omitting blank fields from a report

So there is no way to hind a field if it is blank on a report? i was thinking an IS NULL string but everything I have tried is not working..

 

[GinaWhipp]

You can hide the field but it does not make the row disappear so you report will basically only show rows that have data. From what you have said that is not what you want.

So the two chouces I see are...

1. Fix the tables
2. Write a UNION query and then query on it for Is Null. This will allow the data to be somewhat normalized, thereby using a continuous subreport, at which point only the rows that have data will show.

 

[BettyWho]

The table is not the issue. Even if I seperated the information I would still need to report on all the fields. Which would bring me back to square one. This report encompasses all Schools regardless of whether they have 1 deputy or 4. so when producing the report, if a record does not have a 3 or fourth deputy for example I don't want to the "blank" field to show up on a report. However I can't delete the field from the report entirely as it wont then show the information for the schools that do have more then 1 deputy. Does that make sense?? How would a union query help me?? I'm referring to the report I produce at the end and what you will visually look at as a finished product. the queries I have produce the information perfectly. It's the report that I can't get to display how I need it! Thanks in advance

 

[GinaWhipp]

Okay, I am not explaining it clearly, let's try this...

You say the queries are shwoing the information correctly, do the queries also show the Positions?

 

[BettyWho]

Yes they do. The queries show all the information required. It's when you do a report and combine the information that I can't hide the superfluous fields for those records that don't have that "position" but because some schools have "all positions" I can't hide it by default. Oh the reason I can't do just one query and report on the specific fields is because the report contains ALL schools not just one.. Is that clearer?

 

[GinaWhipp]

Yes, it's clear but because of this set up there is no way to remove ununsed fields without leaving gaps. At least no way that I can think of.

 

[Chrafangau]

The way I dealt with this was to determine which field would be null if the line didnt have the required data, then create this code:

=IIf(IsNull([nullcolumnname]),NULL,[fieldsource])

Set this as the Control Source for each of your fields, replacing fieldsource with their respective sources. That should, if the target field is null, set that text field on the report to null. This done, set the Can Shrink property of all your controls to Yes. Note that this will only work if all the boxes in a horizontal line are null. Finally, set the Can Shrink property of the details section to Yes, to allow it to collapse as fields do. Sorry for the less than clear explanation, but hopefully it points you in the right direction.

 

[BettyWho]

With that ode you've put in the nullcolumnname you refer to is that the table column? because the field source and table have the same "names" Does that make sense? I'm having a serious blonde moment. Could you break it down further for me? Sorry

 

[GinaWhipp]

The suggestion is you put that in the fields in the report. However, that still will not work because you have Labels there and they won't shrink. You would have to turn the Labels into Text boxes and write some code to make them NULL if the other fields aren't filled in.

 

[Chrafangau]

Yes, it's the table column. If you have issues with it just use Tablename.[nullcolumnname]
[fieldsource] is whatever column you want the data for the text box to come from if the field you're checking for blanks is not blank. It can be the same as the field you're checking.
As for the labels, just replace them with text boxes with the same code, except replace [fieldsource] with a literal string in quotes, i.e "Label text here"

 

[BettyWho]

Ok so last night I regenerated the report and removed all the labels and basically just had the fields showing. I've ended up with 4 columns and 9 rows that roughly look like a spread sheet. I have removed all dividing lines and what not to make sure I am not impeding the report can shrink properties. and I'm about to try the code and just want to make sure I've got it right. I'm having a blonde moment and thought best to clarify before I start hitting my head against a brick wall in frustration.

So here is the code you suggested Chrafangau:

=IIf(IsNull([nullFullNameP2]),NULL,[FullNameP2])
Have I got this right?
Thanks

 

[Chrafangau]

Not quite, you want =IIf(IsNull([FullNameP2]),NULL,[FullNameP2])
And then any other fields on the same line are =IIf(IsNull([FullNameP2]),NULL,[OtherSource])