472,142 Members | 1,283 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,142 software developers and data experts.

Report: How to hide field if empty?

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
11 13887
446 Expert 256MB
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.

Jan 20 '12 #2
446 Expert 256MB
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.
Jan 22 '12 #3
32,498 Expert Mod 16PB
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
446 Expert 256MB
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.


Attached Images
File Type: jpg vbCrLf.jpg (7.3 KB, 6646 views)
Jan 23 '12 #5
32,498 Expert Mod 16PB
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
446 Expert 256MB
@ NeoPa, ditto

(answers must be 20 characters!)
Jan 24 '12 #7
32,498 Expert Mod 16PB
Yup ;-)
Jan 24 '12 #8
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
32,498 Expert Mod 16PB
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
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
32,498 Expert Mod 16PB
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.

Similar topics

1 post views Thread by mstery | last post: by
1 post views Thread by Shapper | last post: by
reply views Thread by Kalyan | last post: by
3 posts views Thread by Dentharg | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.