IIf Statement returns Text when null

M

magmike

I have an IIf statement in an unbound text box in a subform that shows
contacts from the Contact table that are related to the Parent form,
which houses data from the Company table.

The IIf statement is extremely long, so i'll only post the part that
is in question:

IIf([Newsletter] = 0,IIf([MonthlyThought] = 0,"","Subscriptions" &
Chr(58) & Chr(13) & Chr(10) & "Monthly Thought"),"Subscriptions" &
Chr(58) & Chr(13) & Chr(10) & "Newsletter" & IIf([MonthlyThought] =
0,"",Chr(13) & Chr(10) & "Monthly Thought"))

Newsletter and MonthlyThought are Yes/No fields that are checked if
the salesman wants to subscribe the contact to either of those monthly
publications. These display in the unbound text field correctly when
there are contacts. When each contact is clicked on (in a continuous
subform) the unbound text box will display one of the following:

Subscriptions:
Newsletter

Subscriptions:
Monthly Thought

Subscriptions:
Newsletter
Monthly Thought

or nothing if neither is checked

NOW the problem - If there aren't contacts in the subform (because
there are none related from the contact table to the company record in
the company table) then the unbound text box shows the last of the
examples. The rest of the IIf statement preceding this part, does not
act this way. If there are no contacts, the rest of the IIf statement
returns "" except this part.

What did I do wrong?

Thanks in advance!

magmike7

Just in case you are curious - here is the entire code for the unbound
text box:

=IIf([ContactGoBy] Is Not Null,Chr(34) & [ContactGoBy] & Chr(34) &
Chr(13) & Chr(10),"") & IIf([ContactPrefix] Is Not Null,
[ContactPrefix] & " ","") & IIf([ContactFirstName] Is Not Null,
[ContactFirstName] & " ","") & IIf([ContactMiddleName] Is Not Null,
[ContactMiddleName] & " ","") & IIf([ContactLastName] Is Not Null,
[ContactLastName],"") & IIf([ContactSuffix] Is Not Null,", " &
[ContactSuffix] & Chr(13) & Chr(10),Chr(13) & Chr(10)) &
IIf([ContactPronunciation] Is Not Null,"Pron: " & Chr(40) &
[ContactPronunciation] & Chr(41) & Chr(13) & Chr(10)) &
IIf([ContactTitle] Is Not Null,[ContactTitle] & Chr(13) & Chr(10) &
Chr(13) & Chr(10),Chr(13) & Chr(10)) & IIf([ContactDirectPhone] Is Not
Null,[ContactDirectPhone] & " phone" & Chr(13) & Chr(10),"") &
IIf([ContactExtension] Is Not Null,"xt. " & [ContactExtension] &
Chr(13) & Chr(10),"") & IIf([ContactCell] Is Not Null,[ContactCell] &
" cell" & Chr(13) & Chr(10),"") & IIf([ContactHomePhone] Is Not Null,
[ContactHomePhone] & " home" & Chr(13) & Chr(10),"") &
IIf([ContactFax] Is Not Null,[ContactFax] & " fax" & Chr(13) & Chr(10)
& Chr(13) & Chr(10),Chr(13) & Chr(10)) & IIf([ContactEmail] Is Not
Null,[ContactEmail] & Chr(13) & Chr(10),"") & IIf([ContactWeb] Is Not
Null,[ContactWeb] & Chr(13) & Chr(10) & Chr(13) & Chr(10),Chr(13) &
Chr(10)) & IIf([ContactAddress1] Is Not Null,[ContactAddress1] &
Chr(13) & Chr(10),"") & IIf([ContactAddress2] Is Not Null,
[ContactAddress2] & Chr(13) & Chr(10),"") & IIf([ContactCity] Is Not
Null,[ContactCity],"") & IIf([ContactState] Is Not Null,", " &
[ContactState],"") & IIf([ContactZip]>0," " & [ContactZip] & Chr(13) &
Chr(10) & Chr(13) & Chr(10),Chr(13) & Chr(10)) & IIf([ContactSummary]
Is Not Null,"Notes" & Chr(58) & Chr(13) & Chr(10) &
[ContactSummary],"") & IIf([Newsletter] = 0,IIf([MonthlyThought] =
0,"","Subscriptions" & Chr(58) & Chr(13) & Chr(10) & "Monthly
Thought"),"Subscriptions" & Chr(58) & Chr(13) & Chr(10) & "Newsletter"
& IIf([MonthlyThought] = 0,"",Chr(13) & Chr(10) & "Monthly Thought"))
 
J

Jeanette Cunningham

Magmike,
this is very long and complicated code for anyone to check out.
There is a sample database on the topic of snarky nested IIf statements
called TwelveBalls - A fun problem in Nasty Nested IF statements
at
http://www.rogersaccesslibrary.com/download3.asp?SampleName=TwelveBalls.mdb
You might be able to use this sample to check your code and find where the
error is.

Jeanette Cunningham

magmike said:
I have an IIf statement in an unbound text box in a subform that shows
contacts from the Contact table that are related to the Parent form,
which houses data from the Company table.

The IIf statement is extremely long, so i'll only post the part that
is in question:

IIf([Newsletter] = 0,IIf([MonthlyThought] = 0,"","Subscriptions" &
Chr(58) & Chr(13) & Chr(10) & "Monthly Thought"),"Subscriptions" &
Chr(58) & Chr(13) & Chr(10) & "Newsletter" & IIf([MonthlyThought] =
0,"",Chr(13) & Chr(10) & "Monthly Thought"))

Newsletter and MonthlyThought are Yes/No fields that are checked if
the salesman wants to subscribe the contact to either of those monthly
publications. These display in the unbound text field correctly when
there are contacts. When each contact is clicked on (in a continuous
subform) the unbound text box will display one of the following:

Subscriptions:
Newsletter

Subscriptions:
Monthly Thought

Subscriptions:
Newsletter
Monthly Thought

or nothing if neither is checked

NOW the problem - If there aren't contacts in the subform (because
there are none related from the contact table to the company record in
the company table) then the unbound text box shows the last of the
examples. The rest of the IIf statement preceding this part, does not
act this way. If there are no contacts, the rest of the IIf statement
returns "" except this part.

What did I do wrong?

Thanks in advance!

magmike7

Just in case you are curious - here is the entire code for the unbound
text box:

=IIf([ContactGoBy] Is Not Null,Chr(34) & [ContactGoBy] & Chr(34) &
Chr(13) & Chr(10),"") & IIf([ContactPrefix] Is Not Null,
[ContactPrefix] & " ","") & IIf([ContactFirstName] Is Not Null,
[ContactFirstName] & " ","") & IIf([ContactMiddleName] Is Not Null,
[ContactMiddleName] & " ","") & IIf([ContactLastName] Is Not Null,
[ContactLastName],"") & IIf([ContactSuffix] Is Not Null,", " &
[ContactSuffix] & Chr(13) & Chr(10),Chr(13) & Chr(10)) &
IIf([ContactPronunciation] Is Not Null,"Pron: " & Chr(40) &
[ContactPronunciation] & Chr(41) & Chr(13) & Chr(10)) &
IIf([ContactTitle] Is Not Null,[ContactTitle] & Chr(13) & Chr(10) &
Chr(13) & Chr(10),Chr(13) & Chr(10)) & IIf([ContactDirectPhone] Is Not
Null,[ContactDirectPhone] & " phone" & Chr(13) & Chr(10),"") &
IIf([ContactExtension] Is Not Null,"xt. " & [ContactExtension] &
Chr(13) & Chr(10),"") & IIf([ContactCell] Is Not Null,[ContactCell] &
" cell" & Chr(13) & Chr(10),"") & IIf([ContactHomePhone] Is Not Null,
[ContactHomePhone] & " home" & Chr(13) & Chr(10),"") &
IIf([ContactFax] Is Not Null,[ContactFax] & " fax" & Chr(13) & Chr(10)
& Chr(13) & Chr(10),Chr(13) & Chr(10)) & IIf([ContactEmail] Is Not
Null,[ContactEmail] & Chr(13) & Chr(10),"") & IIf([ContactWeb] Is Not
Null,[ContactWeb] & Chr(13) & Chr(10) & Chr(13) & Chr(10),Chr(13) &
Chr(10)) & IIf([ContactAddress1] Is Not Null,[ContactAddress1] &
Chr(13) & Chr(10),"") & IIf([ContactAddress2] Is Not Null,
[ContactAddress2] & Chr(13) & Chr(10),"") & IIf([ContactCity] Is Not
Null,[ContactCity],"") & IIf([ContactState] Is Not Null,", " &
[ContactState],"") & IIf([ContactZip]>0," " & [ContactZip] & Chr(13) &
Chr(10) & Chr(13) & Chr(10),Chr(13) & Chr(10)) & IIf([ContactSummary]
Is Not Null,"Notes" & Chr(58) & Chr(13) & Chr(10) &
[ContactSummary],"") & IIf([Newsletter] = 0,IIf([MonthlyThought] =
0,"","Subscriptions" & Chr(58) & Chr(13) & Chr(10) & "Monthly
Thought"),"Subscriptions" & Chr(58) & Chr(13) & Chr(10) & "Newsletter"
& IIf([MonthlyThought] = 0,"",Chr(13) & Chr(10) & "Monthly Thought"))
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Address box with IIf Statement 7
Report control: hide blank row when null data 2
Trouble with chr(13) & chr(10) in IIF statement 3
IIf statement 2
Hlp Pls 1
How to "skip a blank field" when combining text 6
Errors with text box formulas 4
# Error in Unbound text box. 5

Top