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

[pmonreal]

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

[ADezii]

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

In order to do what you are requesting, you would have to dynamically change the ControlSource relating to the [Cell Phone] Field to that of the [EMail] Field when the condition is met (IsNull([Cell Phone]) And Not IsNull([EMail])). This cannot be done in either the Format() or Print() Events of the Detail Section.

[FishVal]

Hi, there.

Try smthng like this

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    With Me
        If IsNull(.[Cell Phone]) And (Not IsNull(.[eMailAddress])) Then
            .[Cell Phone Label].Visible = False
            .[eMailAddress].Top = .[Cell Phone].Top
            .[eMailAddress Label].Top = .[Cell Phone Label].Top
        End If
    End With
End Sub

Good luck.

[pmonreal]

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.

[FishVal]

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.

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.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    With Me
        If IsNull(.[Cell Phone]) And (Not IsNull(.[eMailAddress])) Then
            .[Cell Phone Label].Visible = False
            .[eMailAddress].Top = .[Cell Phone].Tag
            .[eMailAddress Label].Top = .[Cell Phone Label].Tag
        Else
            .[Cell Phone Label].Visible = True
            .[eMailAddress].Top = .[eMailAddress].Tag
            .[eMailAddress Label].Top = .[eMailAddress].Tag
        End If
    End With
End Sub

Private Sub Report_Open(Cancel As Integer)
    With Me
        .[Cell Phone].Tag = .[Cell Phone].Top
        .[eMailAddress Label].Tag = .[eMailAddress Label].Top
    End With
End Sub

[ADezii]

Hi, there.

Try smthng like this

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    With Me
        If IsNull(.[Cell Phone]) And (Not IsNull(.[eMailAddress])) Then
            .[Cell Phone Label].Visible = False
            .[eMailAddress].Top = .[Cell Phone].Top
            .[eMailAddress Label].Top = .[Cell Phone Label].Top
        End If
    End With
End Sub

Good luck.

Nice solution, FishVal!

[FishVal]

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

[ADezii]

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. :)

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.

[FishVal]

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.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    With Me
        If IsNull(.[Cell Phone]) And (Not IsNull(.[eMailAddress])) Then
            .[Cell Phone Label].Visible = False
            .[eMailAddress].Top = .[Cell Phone].Tag
            .[eMailAddress Label].Top = .[Cell Phone Label].Tag
        Else
            .[Cell Phone Label].Visible = True
            .[eMailAddress].Top = .[eMailAddress].Tag
            .[eMailAddress Label].Top = .[eMailAddress].Tag
        End If
    End With
End Sub

Private Sub Report_Open(Cancel As Integer)
    With Me
        .[Cell Phone].Tag = .[Cell Phone].Top
        .[eMailAddress Label].Tag = .[eMailAddress Label].Top
    End With
End Sub

Sorry. There are typos in a code.
Actually it should look like

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    With Me
         If IsNull(.[Cell Phone]) And (Not IsNull(.[eMailAddress])) Then
            .[Cell Phone Label].Visible = False
            .[eMailAddress].Top = .[Cell Phone].Tag
            .[eMailAddress Label].Top = .[Cell Phone].Tag
        Else
            .[Cell Phone Label].Visible = True
            .[eMailAddress].Top = .[eMailAddress].Tag
            .[eMailAddress Label].Top = .[eMailAddress].Tag
        End If
    End With
End Sub

Private Sub Report_Open(Cancel As Integer)
    With Me
        .[Cell Phone].Tag = .[Cell Phone].Top
        .[eMailAddress].Tag = .[eMailAddress].Top
    End With
End Sub

[FishVal]

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 ?? :))))))))))

[ADezii]

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

Not until recently, when I became involved with TheScripts Forum. (LOL).

[pmonreal]

Thank you so much! I added a little more code to handle a last condition, and here is the final code that works!

Private Sub Detail_Format(C ancel As Integer, FormatCount As Integer)
With Me
If IsNull(.[Cell]) And (Not IsNull(.[eMailAddress])) Then
.[CellLabel].Visible = False
.[eMailLabel].Visible = True
.[eMailAddress].Top = .[Cell].Tag
.[eMailLabel].Top = .[Cell].Tag
ElseIf IsNull(.[Cell]) And (IsNull(.[eMailAddress])) Then
.[CellLabel].Visible = False
.[eMailLabel].Visible = False
Else
.[CellLabel].Visible = True
.[eMailLabel].Visible = True
.[eMailAddress].Top = .[eMailLabel].Tag
.[eMailLabel].Top = .[eMailLabel].Tag
End If
End With
End Sub

Private Sub Report_Open(Can cel As Integer)
With Me
.[Cell].Tag = .[Cell].Top
.[eMailLabel].Tag = .[eMailLabel].Top
End With
End Sub
Thanks again! You saved me! Happy Labor Day!

[FishVal]

You are welcome. Good luck.