"Moving" a line in a Report based on 2 conditions

I am developing a simple report that lists Name, Address, Home Phone, Cell Phone and eMail address. The format is like the “Address Cards” format in Outlook (see below). Many of the people on the roster have no cell phone and/or eMailAddress. I used a text box for the “label” for the cell phone and email fields. I used the following for the cell phone “label”: =IIf(IsNull([Cell])," ","Cell Phone:") so that if there is no cell phone number, the “label” doesn’t print. I have similar code in the email field. If both cell phone and email fields are null, it works great

The problem arises when there is an email address (bottom line) but no cell phone number (2nd last line). I end up with a blank line separating the email address from the rest of the lines. Is there a way that I can code the email address “label’ (text box) to say that if the cell phone field is null, but the email isn’t, to move the email address up to where the cell phone entry would have been? I thought maybe I could use the “Top” property, but I can’t find in any of the help how to test for 2 conditions. I couldn’t figure out a way to do it via nested Iifs either.

I would really appreciate some help. Thanks!

Name
Address
City, State, Zip
Home Phone
Cell Phone
eMailAddress

Wow, thanks for the quick response! The code you provided worked when there was an email address, but no cell number. However, when there is both a cell number and email address, the email address overlays the cell number.

Hi, there.

Try smthng like this

Expand|Select|Wrap|Line Numbers

1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
2.     With Me
3.         If IsNull(.[Cell Phone]) And (Not IsNull(.[eMailAddress])) Then
4.             .[Cell Phone Label].Visible = False
5.             .[eMailAddress].Top = .[Cell Phone].Top
6.             .[eMailAddress Label].Top = .[Cell Phone Label].Top
7.         End If
8.     End With
9. End Sub
10.  

Good luck.

Nice solution, FishVal!

Thanks, ADezii.

Actually this looks like stub so far.

I thought about more general and reusable code but it seems to me that amount of code and execution time ;) grow exponentially approaching to more or less universal solution.

I think the really nice solution here is to reorganize table structure.
Really, how many people do you know that have only one e-mail or one phone number. :)

Regards

Fish

Really, how many people do you know that have only one e-mail or one phone number. :)

Ok.

You just need to restore the controls position/visibility. For this purpose you need to store original eMail controls position. You can use store them in global variables or in the controls Tag property.

Expand|Select|Wrap|Line Numbers

1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
2.     With Me
3.         If IsNull(.[Cell Phone]) And (Not IsNull(.[eMailAddress])) Then
4.             .[Cell Phone Label].Visible = False
5.             .[eMailAddress].Top = .[Cell Phone].Tag
6.             .[eMailAddress Label].Top = .[Cell Phone Label].Tag
7.         Else
8.             .[Cell Phone Label].Visible = True
9.             .[eMailAddress].Top = .[eMailAddress].Tag
10.             .[eMailAddress Label].Top = .[eMailAddress].Tag
11.         End If
12.     End With
13. End Sub
14.  
15. Private Sub Report_Open(Cancel As Integer)
16.     With Me
17.         .[Cell Phone].Tag = .[Cell Phone].Top
18.         .[eMailAddress Label].Tag = .[eMailAddress Label].Top
19.     End With
20. End Sub
21.  

Believe it or not, when I was young there was no such thing as an E-Mail Address and a cell phone was considered a luxury! LOL.

Did you suffer hard ?? :))))))))))