Iif help

[Guest]

I have a database that has 2000+ contacts including names, addresses, phone
numbers, etc. I have created a report to act as a phone book, and in the
interest of saving space, I have used IIF statements to display only the
fields that have data in them, and I have made the fields very small and set
them to 'grow' if necessary; for example, I may have a work phone # for
someone, but not a mobile, and vice versa. My IIF statement is as follows:

=IIf(Not IsNull([WWorkPhone]),"Work Phone: " & [WWorkPhone] & Chr(13) &
Chr(10) & IIf(Not IsNull([WWorkFax]),"Work Fax: " & [WWorkFax] & Chr(13) &
Chr(10) & IIf(Not IsNull([WMobile]),"Mobile: " & [WMobile] & Chr(13) &
Chr(10) & IIf(Not IsNull([WPager]),"Pager: " & [WPager] & Chr(13) & Chr(10) &
IIf(Not IsNull([WFamilyName]),"Family: " & [WFamilyName] & ", " &
[WFamilyPhone])))))

The problem is that if there is no work phone, none of the following data is
printed (mobile, pager, etc.)
Is there a way to have more than one iif statement in a field that is not
dependent on the first?

Thanks for any help.
Lisa

 

[Rick B]

answered in your 12:37 post

 

[Larry Daugherty]

Hi,

I'm not sure when IIF runs out of steam but you should be telling it what to
do if the condition is met and also what to do if the condition is not met.
That implies writing two sets of code at each branch and for all of the
branches that follow. I believe there should be more than twice the code
you have because you should write the code for both true and false for all
subsequent branches at each branch. I'd rather do something else than code
all those nested IIFs!

It looks like your schema is twisted a bit toward spreadsheet/flat file
think rather than relational database think: Every time you needed a new
phone type you "added another column", Actually you added another field.

There is a similar but different way to achieve what you want: Create a
calculated field in the report's query, call it Phone: including the colon.
then add in the IIF result of each field in turn - something like:

Phone: IIf(Not IsNull([WWorkPhone]),"Work Phone: " & [WWorkPhone] & Chr(13)
&
Chr(10),"") & IIf(Not IsNull([WWorkFax]),"Work Fax: " & [WWorkFax] &
Chr(13) &
Chr(10),"") That's just a couple of fields but you get the idea.

Notice that the above is essentially your code but that just one field
tested and the result of testing the condition is added into the text before
testing the next field.

On your report make the new field "Phone:" the source for the control.

I believe the above is probably the least amount of work to get from where
you are to where you want to be.

FYI: You could also take a different path to make your application more
relational if you choose. Add another entity/table to your schema: tblPhone
for want of a better name and a lookup table, tblPhonePurpose to hold a list
of all possible phone purposes or types: Home, Home2,Work, HomeFax, WorkFax,
WorkFax2. The primary key of tblContact appears as a long integer foreign
key in tblPhone. You would establish a parent/child relationship between
tblPhone and tblContact. Once the relationship is established you can
create a subform on your frmContact. One at a time you can select the
phone's purpose from tblPhonePurpose to put its text into text box and then
enter the related phone number. Continue for as many phones as that contact
has.

HTH
--
-Larry-
--

I have a database that has 2000+ contacts including names, addresses, phone
numbers, etc. I have created a report to act as a phone book, and in the
interest of saving space, I have used IIF statements to display only the
fields that have data in them, and I have made the fields very small and set
them to 'grow' if necessary; for example, I may have a work phone # for
someone, but not a mobile, and vice versa. My IIF statement is as follows:

=IIf(Not IsNull([WWorkPhone]),"Work Phone: " & [WWorkPhone] & Chr(13) &
Chr(10) & IIf(Not IsNull([WWorkFax]),"Work Fax: " & [WWorkFax] & Chr(13) &
Chr(10) & IIf(Not IsNull([WMobile]),"Mobile: " & [WMobile] & Chr(13) &
Chr(10) & IIf(Not IsNull([WPager]),"Pager: " & [WPager] & Chr(13) & Chr(10) &
IIf(Not IsNull([WFamilyName]),"Family: " & [WFamilyName] & ", " &
[WFamilyPhone])))))

The problem is that if there is no work phone, none of the following data is
printed (mobile, pager, etc.)
Is there a way to have more than one iif statement in a field that is not
dependent on the first?

Thanks for any help.
Lisa