By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,391 Members | 1,429 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,391 IT Pros & Developers. It's quick & easy.

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

P: 3
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
Sep 1 '07 #1
Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,669
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.
Sep 2 '07 #2

FishVal
Expert 2.5K+
P: 2,653
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.
Sep 2 '07 #3

P: 3
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.
Sep 2 '07 #4

FishVal
Expert 2.5K+
P: 2,653
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.

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.  
Sep 2 '07 #5

ADezii
Expert 5K+
P: 8,669
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!
Sep 2 '07 #6

FishVal
Expert 2.5K+
P: 2,653
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
Sep 2 '07 #7

ADezii
Expert 5K+
P: 8,669
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.
Sep 2 '07 #8

FishVal
Expert 2.5K+
P: 2,653
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.  

Sorry. There are typos in a code.
Actually it should look like
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].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].Tag = .[eMailAddress].Top
  19.     End With
  20. End Sub
  21.  
Sep 2 '07 #9

FishVal
Expert 2.5K+
P: 2,653
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 ?? :))))))))))
Sep 2 '07 #10

ADezii
Expert 5K+
P: 8,669
Did you suffer hard ?? :))))))))))
Not until recently, when I became involved with TheScripts Forum. (LOL).
Sep 2 '07 #11

P: 3
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(Cancel 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(Cancel As Integer)
With Me
.[Cell].Tag = .[Cell].Top
.[eMailLabel].Tag = .[eMailLabel].Top
End With
End Sub
Thanks again! You saved me! Happy Labor Day!
Sep 3 '07 #12

FishVal
Expert 2.5K+
P: 2,653
You are welcome. Good luck.
Sep 3 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.