Checking if a field is empty 2

[minli98]

Hi,

I am trying to create a report in Access for the first time, and am hitting into this problem:

I have a field called Address2 which are often null (or no value). So, in the report I want to print it if it has a value and skip if it's empty/null. I tried the following expression:

=[Address1] & Chr(13) & Chr(10)
& iif(isNull([Address2]),"", [Address2] & Chr(13) & Chr(10))
& [City] & ", " & [State] & " " & [ZipCode]

But this expression gives me an error. The expression isNull([Address2]) always returns false (even for those Address2 that are empty), and when I tried, printing the field gives me an error.

I have played around with IsEmpty, IsObject, and IsMissing, but nothing works.

Could anyone spot the problem? Many thanks in advance.

Regards,
Min

 

[THWatson]

Min
Try this for the first line...
=[Address1] & "
" & IIf(IsNull([Address2]),"",[Address2])
for the first part, and then put your City, State and ZipCode in a separate text box on a new line.

Note that on the first line in the text box there is a <ctrl> + <enter> following the ". In other words, between the " at the end of the first line in my suggestion and the " at the start of the second line in my suggestion there is <ctrl> + <enter>.

Tom

 

[minli98]

Hi Tom,

My problem is with the IIf(IsNull([Address2]),"",[Address2]) part.

Somehow IsNull([Address2]) always return false, but I can't print =[Address2]. That expression always gives an error.

The only way to print Address2 is on its own text box with the control source pointed to Address2 (without the equal sign or square brackets).

Btw, if I put City, State, ZipCode in a separate textbox, would there be a blank line between address1 and this textbox if address2 is blank?

Thanks for your help.

Regards,
Min

 

[THWatson]

Min
Did you try what I suggested? That will force a second line if there is an [Address2] but won't leave a blank line if there is no [Address2].

What happens is that the text box containing the expression I suggested is only one line high, the same height as the Name line (which I assume precedes your Address1 line), and the City, State and ZipCode line which follows on a separate line...but when there is an [Address2] it should print on a second line.

Tom

 

[minli98]

Tom,

Yes, I follow your exact syntax, but it gives me #Error result. And just to verify, when you say put <Ctrl>+<Enter>, you mean that I press those keys to force the rest of the expression into the second line right?

Min

 

[THWatson]

Min
By the way...make sure to set the text box property to "Can Grow" because if you don't do that it won't work.

Perhaps that was the problem with your original expression. Because it does appear to me that should work as well as what I suggested.

Tom

 

[THWatson]

Min
In your reply to your last post...yes, that is what I meant.

You could just copy what I posted and paste it in the control source.

However, make sure about the Can Grow first.

Tom

 

[minli98]

Tom,

Your suggestion works for other fields, just not Address2. So the problem is with this particular field and not with the syntax. I will play around and see what's wrong with it.

Thanks for your help!

Regards,
Min

 

[Remou]

Did you paste THWatson's code into a text box called Address2? If so, change the name to anything other than Address2.

 

[minli98]

Yes, turned out that was the problem. I was really scratching my head over it. I tried changing the field name (not the text box name), but that didn't work.

Thanks to both of you. I get it working now.

Regards,
Min