Code for if a field is null, don't print?

[DanicaDear]

Ok everyone, I hope I finally have a question with a simple answer. :-)
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?
Thanks in advance!!!!!!!!!!

[NeoPa]

@DanicaDear
Not by a Light-Year Danica.
@DanicaDear
How do you have the report formatted? Which controls are where relative to the others? Remember to include the Label controls as well as the TextBoxes.

One technique that may work (depends on answer to questions) is to use a TextBox control of multiple lines and the .CanGrow/.CanShrink properties. Otherwise I can only think of using some fairly intricate VBA coding.

[DanicaDear]

LOL. I guess I thought there would be some magic VBA/expression.

I am not sure that this is worth my or your time. It would be a "bell and whistle" as we call it in the USA. I'll be honest I would like to learn "intricate VBA coding" but on the other hand I realize I am not going to be able to write the world's fanciest software package on my very first DB. You probably aren't impressed at all but I'm impressed with what I have done so far, since just 3 weeks ago I had never clicked a button in Access. Anyway...back to the issue here...you have probably uploaded my DB from another post so feel free to use that. In the report rptSTICKS_NOT_RETURNED, you will find this address issue on two of the results. When prompted for [Days since last shipment] parameter input, use "60". Thanks once again for patience and time.

[Delerna]

Hi Dancia
I took a look at this as I was checking out your other issue.
Hmmm, yes its possible with VBA but by no means trival to accomplish what you are asking.
It would involve checking the character length in each address field
and adjusting the size and position of the textbox controls.


I think you would be better off concattenating all of the fields involved into 1 or 2 fields in the query that the report is based off and then redesign your report to suit.

Or maybe concattenating all 3 address fields into 1 in the query
Or any number of other combinations (in the query)

[NeoPa]

To use VBA you would need to check the contents of the three address controls. If the last were empty then you would need to set it to be .Visible=No and then subtract the difference between the .Top of that control, and the .Top of the next one down from it, from all controls that are lower in the section. Then you need to reduce the section .Height by that same value of course. Unfortunately, you need to do this in such a way that it is returned for another record where that control isn't Null. Sound like fun? Well, it is. I've done it & it certainly is. It's not too straightforward though. Not something you'd want to get into at an early stage unless you are quite brave and prepared to swim in deep waters for a bit.

[DanicaDear]

I tried to come up with something that might work. Since I’m new at writing any type of expression, I thought it might be useful to kind of guide you through what I am trying to do, and then put what I think is some coding that might work. I am using an expression inside an expression, and for the responder’s ease I have underlined the inner expression. I’m new so feel free to let me know what errors I made. (Note: There will always be a value in address1. City_ST_Zip is already formed into one line in a query.)

iif([address3] is null, print [Whole_Name] & new line & [address1] & new line & (iif([address2] is null, print [City_ST_Zip], otherwise print [address2] & new line & [City_ST_Zip])), otherwise print [Whole_Name] & new line & [address1] & new line & [address2] & new line & [address3] & new line & [City_ST_Zip])
 
 
Iif(IsNull([ADDR3]), [Whole_Name] & vbCrLf & [ADDR1] & vbCrLf & (iif(IsNull([ADDR2]), [citySTzip], [address2] & vbCrLf & [citySTzip])), [Whole_Name] vbCrLf & [ADDR1] & vbCrLf & [ADDR2] & vbCrLf & [ADDR3] & vbCrLf & [City_ST_Zip])

I don’t know where to put this code to make it work. The idea is to see the end result in a report. Not only do I not know which object to put it in (query/form/report), I don’t know where to put it inside that object (in the properties box, on click, etc?). I am sort of thinking I will have to make a large text box on the report but I don’t know how to link it back to this.

Will this even work at all? Sometimes thinking too much gets me in trouble. Hahahaha.

[NeoPa]

Danica,

First I must give a last unofficial warning about using CODE tags. You're a full member now & I've had to edit your posts on a number of occasions already. I'm loathe to come all heavy on you, but these rules must be obeyed, particularly for the sake of any expert that's trying to help you. Next one will have to be official I'm afraid.

Moving on :
As I mentioned before there are .CanGrow & .CanShrink properties of various objects (including controls and even sections). I never use them myself, but I believe with a little experimentation you can get something to work for you.

As far as the contents of the (probably TextBox) control is concerned, you can produce an [Addr] field in your query that relies on Null propagation with the + concatenation character (See Using "&" and "+" in WHERE Clause) and consists of :

Addr: [Addr1] & (Chr(13) & Chr(10) + [Addr2]) & (Chr(13) & Chr(10) + [Addr3])

The Carriage Returns and Line feeds only get through to the result if the corresponding [Addr?] value is not Null.

[DanicaDear]

What is the CODE tag you refer to? Is it where I'm trying to underline? I thought previously you were talking about something in my program...what do the code tags do when I place them on Bytes?

[DanicaDear]

I think I now understand that the CODE tags are where you are putting my code in the special boxes and honestly I was wondering how it so neatly went into that box. I still don't know the proper way to do this although I did find the Standard Editor you referred to. I suppose you use these code tags anytime you list code/expressions of any sort. What happens when I don't use the tags? Also, where are the rules for this site (so I would know I must use code tags, for instance)?

[NeoPa]

I will see if I can prepare some extra info on the CODE tags for you Danica. In the mean-time, there is a link on every page of the site (top-right where it says Help | Site Map | Sign Out) to the rules of the site.

[NeoPa]

Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page).

The simplest way to use CODE tags is to select the code in your post and then click on the button above the post window which looks like the (#) character. This has been programmed to do it for you. The Code Box is designed to show the code in non-proportional font, so that it's easier to see what matches what. It also uses a font where the characters are clearly recognisable. In the standard post window for instance, it is very hard to see whether ('') is one of (") or two of ('). Seeing the same in the Code Box is immediately clear however.

In the standard post window for instance, it is very hard to see whether ('') is one of (") or two of (').

I'm happy to answer any questions on rules or site usability. It is in everybody's interest that they're followed, so anything I can do to help - just ask.

[DanicaDear]

Okay, I think I understand this now.

Also, I see now you clearly explained this before but I didn't understand it. So thanks for clearing it up.

[DanicaDear]

NeoPa, your code for the query worked. I put a text box in the report and linked it to the query. I didn't really have to use the Can Shrink property because I added the zip code the same way you added the 3 address lines. It takes out all empty lines automatically. There is nothing under the text box in the report so I guess it has no need to shrink. Thank you for your continued support! I mean REALLY REALLY thank you! :-)

[NeoPa]

Not a problem Danica.

I'm very pleased to see you have grasped the CODE issue. To be honest, you have always had a very pleasant attitude, so it's been a pleasure helping you along a littel.

[DanicaDear]

I got the Can Grow/Can Shrink properties working in rptCUSTOMER_LABELS and somewhat in rptHOTSTICK_CUSTOMER_INQUIRY. I'd like to direct your attention to rptHOTSTICK_CUSTOMER_INQUIRY. I have the address in the CUST_NUM header because I only want the address to appear once for each customer's orders. The grow/shrink trick doesn't seem to work. Just to try it out, I copied the controls into the Detail portion of my report and they work perfectly fine. Are you not allowed to shrink/grow in a header? Otherwise, what's my issue here? File is attached.

(I know you missed me yesterday. ;-) Getting better = less questions.)

[NeoPa]

I think the reason the Header section is not shrinking is because you have some label controls lower down anyway. They are positioned statically. There is no question of them moving relative to the controls above - hence the section stays at its defined height. I appreciate of course, that the answer doesn't help you greatly, as you probably don't want to lose the labels from where they are, and there seems to be no alternative that lets you shrink the section.

I looked at rptCUSTOMER_LABELS to see what was different there. There are no extra labels. When I tested this by removing the other controls in the section in rptHOTSTICK_CUSTOMER_INQUIRY it did, in fact, shrink to fit.

[DanicaDear]

That is very interesting. I have played with it and I see what you mean. Maybe with some more thinking I can come up with a work-around or improved design. Thank you NeoPa for another excellent reply.

[NeoPa]

Sorry I couldn't provide something to get you past the problem.

[DanicaDear]

Don't be sorry, NeoPa. You provided me the exact solution I needed. The rest is up to me. I have rearranged my page and things are working fine. Thanks again, for everything! I cannot possibly give you enough thanks for what you have done for me. Do you have a fan club? ;-)