By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,304 Members | 1,739 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,304 IT Pros & Developers. It's quick & easy.

Report: How to hide field if empty?

P: 1
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?
Jan 20 '12 #1
Share this Question
Share on Google+
11 Replies


Expert 100+
P: 446
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
Jan 20 '12 #2

Expert 100+
P: 446
Hi agian,
The alternative to my last post is to concatonate the three address lines thus.
Expand|Select|Wrap|Line Numbers
  1. =[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
Jan 22 '12 #3

NeoPa
Expert Mod 15k+
P: 31,707
@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 :
Expand|Select|Wrap|Line Numbers
  1. =[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.
Jan 23 '12 #4

Expert 100+
P: 446
@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

Attached Images
File Type: jpg vbCrLf.jpg (7.3 KB, 6220 views)
Jan 23 '12 #5

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jan 23 '12 #6

Expert 100+
P: 446
@ NeoPa, ditto


(answers must be 20 characters!)
Jan 24 '12 #7

NeoPa
Expert Mod 15k+
P: 31,707
Yup ;-)
Jan 24 '12 #8

P: 11
I think it would be better to put this logic in the query upon which the report is based rather than a text box.
Jan 24 '12 #9

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jan 24 '12 #10

P: 11
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.
Jan 25 '12 #11

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jan 25 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.