Put what, where, to eliminate blank spaces in sub-report

[EdK]

Hello, all. I am looking for help with a certain report I have designed (without VBA knowledge). The report works OK but leaves a blank space in its sub-report, for each record found that does NOT have each of its five fields return positive (does that make sense?). I know about setting a report's event property field "On no data" = visible = false. But that doesn't work for the subreport. What I am looking for, instead of the above, is to tell each of the five fields of the sub-report to be invisible when the field has no data. I can't figure out what or where to put an appropriate instruction, but I fancy it will be simple to do for you guys/gals. The field names are: endnote1, endnote2, endnote3, endnote4, endnote5. Preferably, if possible, if would be better for me to make it work if you just tell me what expression to enter in each field's property sheet, rather than code. Hope you can help. EdK

 

[sneuberg]

If the empty fields are null, not empty strings, maybe if you added this:

 Not (endnote1 Is Null AND endnote2 Is Null AND endnote3 Is Null AND endnote4 Is Null AND endnote5 Is Null)

To the WHERE clause of the subreport's Record Source query it would help'

I'm guess that some other field has data in it that's not being included in this subreport Why is it that?

 

[EdK]

Hello again. Thank you to Sneuberg for your answer but unfortunately I haven’t been able to make that work. Doesn’t matter, let’s start again. This time I will give a bit more explanation/background.

The sub-report is in a report footer which has a linking field called para1ref, all this does is make sure the right endnote(s) gets put in the book paragraph report against the right paragraph number. No problem with that, it works fine.

The sub-report also has five fields in it which may or may not have data in them (depending on what I have input against the particular paragraph, using my “Booktext Form” Form. These fields are named endnote1, endnote2, endnote3, endnote4, endnote5. I always enter data into these fields in that order ie if that paragraph has one endnote to it, it will always be entered into field endnote1. If the paragraph has two endnotes, then fields endnote1 and endnote2 will have data in them. And so on. Very simple and logical.

My little problem is a formatting one - at present I can only get to apply the instruction “on no data = visible = false” across the whole of the sub-report (the footer of the main report). This only works when ALL FIVE of the endnote fields have no data – when that is the case, all five endnote fields become invisible and shrink, as I want. But at present, I have no way of making eg endnote2 to endnote5 shrink when endnote1 has data in it. Savvy?

What I want to happen is as follows (to put it absolutely clearly):

a) if no data in all of fields endnote1 to endnote5 then =visible=false and all 5 endnote fields shrink to nothing, thus eliminating the annoying blank space at the end of the report [as mentioned, this aspect IS working OK at present, but needs to be included in new considerations just the same)
b) if data in field endnote 1, then endnotes 2 to 5 need to be = visible = false and shrink to nothing when the whole report is printed off
c) if data in fields endnote1 & 2, then endnotes 3 to 5 need to be = visible = false and shrink to nothing when the whole report is printed off
d) if data in fields endnote 1 to 3, then endnotes 4 & 5 need to be = visible = false and shrink to nothing when the whole report is printed off
e) if data in fields endnote 1 to 4, then endnote 5 needs to be = visible = false and shrink to nothing when the whole report is printed off
f) if data in fields endnote 1 to 5 (this happens sometimes) then the above routine is n/a

The report works extremely well except for the little glitch described above. I keep thinking this can’t be too hard to do but it’s beyond me.

If, as seems to be the case, VBA code needs to be resorted to, I’m afraid I will need to be told exactly what code to use, exactly where to place the code, and possibly even how to open the VBA editor etc or whatever is needed.

It is just a nuisance ie creates perhaps an extra blank space of 1 inch at the end of any paragraph with just data in endnote1 field. But across a book of (say) 300 paragraphs, that is an awful lot of unwanted blank space!!

Hope this makes sense and that someone out there can help again/this time.

By the way, the endnote fields are Rich Text format, if that is relevant.

EdK