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
12 2271 ADezii 8,834
Recognized Expert Expert
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 2,653
Recognized Expert Specialist
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.
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 2,653
Recognized Expert Specialist
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 8,834
Recognized Expert Expert
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 2,653
Recognized Expert Specialist
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 8,834
Recognized Expert Expert
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 2,653
Recognized Expert Specialist
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 2,653
Recognized Expert Specialist
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 8,834
Recognized Expert Expert
Did you suffer hard ?? :))))))))))
Not until recently, when I became involved with TheScripts Forum. (LOL).
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!
FishVal 2,653
Recognized Expert Specialist
You are welcome. Good luck.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Matt Kruse |
last post by:
This is a common requirement - "freeze panes" in a table, so that some
header rows and some columns on the left are frozen while the body content
scrolls. This makes large tables more usable on...
|
by: Ellen Manning |
last post by:
I've got an A2K report showing students and their costs. Student info
is in the main report and costs are in a subreport for each student.
The user inputs the program desired then only those...
|
by: Matt |
last post by:
I have 2 questions:
1. strlen returns an unsigned (size_t) quantity. Why is an unsigned
value more approprate than a signed value? Why is unsighned value less
appropriate?
2. Would there...
|
by: OJ |
last post by:
It's easy to control one ball (a graphic) to move and bounce inside a square.
But I don't know how to create and control a large number of balls (10 or
100) and count how many times they hit a wall...
|
by: MikeJ |
last post by:
In my office, we have workstations with standard Win 2000 images (OS plus
standard apps like Office, etc.) as developers, we then install our
development tools on top of those images. other...
| |
by: Ralf Kaiser |
last post by:
Hi,
is it possible to define another place where the "Temporary ASP.NET Files"
are stored?
I do not want to have them on my system partition because i have a separate
partition for all the...
|
by: Paul |
last post by:
I am moving an existing app written years ago to a new server. It uses
Sigma Template 1.3 and Quickform 1.1.1 and PEAR.php,v 1.1.1.1 2004/02/16
The directory structure is like this:
/site...
|
by: Gianni Mariani |
last post by:
I'm hoping someone can tell me why using member address of works and why
using the dot operator does not in the code below.
The code below uses the template function resolution mechanism to...
|
by: Micheal |
last post by:
Greetings Access Group,
Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |