Report: How to hide field if empty?

[softnet alake]

In a report, I have fields for customer's address, Address 1, Address 2, Address 3. Very few people use all 3 fields. I am looking for some code to say..."if the field is null, don't print...ie. don't leave a blank line before you show me the city/state/zip. How can I do this?

[sierra7]

Hi
The simplest way to do this (without using code) in a Report is to set the heights of the controls for Address2 and Address3 to zero but have their property 'Can Grow' set to Yes.

This is not perfect because there are still the accumulated spaces between the lines that will push city/state/zip down the page a little but this should not be bad and is an improvement on having a big gap.

S7

[sierra7]

Hi agian,
The alternative to my last post is to concatonate the three address lines thus.

=[Add1] & IIf(Not IsNull([Add2]),Chr(13) & Chr(10) & [Add2],"") & IIf(Not IsNull([Add3]),Chr(13) & Chr(10) & [Add3],"")

I had trouble making this work the other night because I had Chr(10) & Chr(13) the wrong way round, silly me!

These represent Carriage Return and Line Feed, so the next non null field is placed on the next line.

The control into which this is placed as the source code must have Can Grow set to Yes, so it can grow to the required height when three lines are present.

The name of the control cannot be the same as any of the fields it contains e.g. [Add1], [Add2] or [Add3], but calling it txtAdd1 is ok.
S7

[NeoPa]

@S7.
There are two predefined values in VBA for Chr(13) & Chr(10) - vbCrLf and vbNewLine. Either will replace the two in your code (vbCr & vbLf are also available for each individually).

Furthermore, you may like to check out Using "&" and "+" in WHERE Clause for handling Null-resulting string expressions. Here's an illustration of what they can do :

=[Add1] & ([Add2] + vbCrLf) & ([Add3] + vbCrLf)

This will have the same result as your suggested code (which is perfectly correct of course), but a little shorter.

[sierra7]

@NeoPa
I did ofcourse try vbNewLine and vbCrLf before posting but they don't seem to work in the context of textbox controls. Access seems to interpret them as variables and wants a parameter input. (See pic)

The + instead of & is a neat shortcut though.

S7

[NeoPa]

I'm sorry. I thought I'd checked that out (Essentially references from Jet SQL), but clearly I hadn't done so properly. That certainly makes more sense. I would have expected you to know about them, but I didn't want to say anything anyway. I'm still finding things, from time-to-time, that are new to me, but others have been using regularly. What's obvious to one is not always so to all others I find.

[sierra7]

@ NeoPa, ditto


(answers must be 20 characters!)

[NeoPa]

Yup ;-)

[ChrisPadgham]

I think it would be better to put this logic in the query upon which the report is based rather than a text box.

[NeoPa]

That would not make for a very nicely laid out report. In many cases it might make sense, but I doubt it would make much in this scenario.

[ChrisPadgham]

I fail to see how it would have any effect on the layout of the report, the text box on the report would reference the calculated field in the query. You are simply moving the calculations from the form to the query upon which it is based.

[NeoPa]

You're absolutely right Chris.

Put it down to a brain-fart. It's late and I'm tired. I was (mis) remembering that the report was working with multiple controls, but clearly it wasn't.

PS. Like you, I'd generally do it that way. In this case though, help for handling the ControlSource of a control in their existing report was requested (and provided). Your point is well worth making though, nevertheless.