[Corrected version] Expression help - Nested IIf's??

[Steve]

I need help with a line on a report. I have a student
database and on the report I need to list the student's
parents'names. Each student lives with at least one
parent in a Primary Household. He may also have a second
parent in the primary household or a second parent living
in an Other Household.

The parents' names are coming from a query which has four
fields for the parent names: PHParentName1, PHParentName2,
OHParentName1, and OHParentName2. Up to three of these may
be blank (although either PHParentName1 or PHParentName2
will always be non-blank). I need an expression -- and I
have been trying to do it using a series of nested IIf
statements -- that results on the report with a single line
that has the parent names, separated by an ampersand if
there is more than one, e.g. : Smith, Mary & Smith, Joe
[here the student has two parents, both living in the
Primary Household, so that PHParentName1 and PHParentName2
are non-blank while OHParentName1 and OHParentName2 are
blank] OR Smith, Mary & Jones, Bill [here the student has a
mother listed as PHParentName1, a blank PHParent2, a blank
OHParent1 and father is OHParent2].

Can anybody offer any suggestion on how to get this
expression to take into account all the possibilities?

Thanks!

 

[Fons Ponsioen]

I think the following will give you a start:
= IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & " & IIf([PHParentName2] Is Not Null,
[PHParentName2],IIf([OHParentName1] Is Null,
[OHParentName2],[OHParentName1]))
All of it on one line in a report or if you wish to put it
in a query:
Names: IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & " & IIf([PHParentName2] Is Not Null,
[PHParentName2],IIf([OHParentName1] Is Null,
[OHParentName2],[OHParentName1]))
Agan all on one line make sure you remove any > which are
added by this editor.
Hope this helps.
Fons

 

[Fons Ponsioen]

Sorry I found an error:
I think the following will give you a start:
= IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & " & IIf(([PHParentName2] Is Null
And [PHParentName1] Is Not Null) Or ([PHParentName1] Is
Null And [PHParentName2] Is Not Null),(IIf([OHParentName1]
Is Null,[OHParentName2],[OHParentName1])),[PHParentName2])
All of it on one line in a report or if you wish to put it
in a query:
Names: IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & " & IIf(([PHParentName2] Is Null
And [PHParentName1] Is Not Null) Or ([PHParentName1] Is
Null And [PHParentName2] Is Not Null),(IIf([OHParentName1]
Is Null,[OHParentName2],[OHParentName1])),[PHParentName2])
Again all on one line make sure you remove any > which are
added by this editor.
Hope this helps.
Fons

-----Original Message-----
I need help with a line on a report. I have a student
database and on the report I need to list the student's
parents'names. Each student lives with at least one
parent in a Primary Household. He may also have a second
parent in the primary household or a second parent living
in an Other Household.

The parents' names are coming from a query which has four
fields for the parent names: PHParentName1, PHParentName2,
OHParentName1, and OHParentName2. Up to three of these may
be blank (although either PHParentName1 or PHParentName2
will always be non-blank). I need an expression -- and I
have been trying to do it using a series of nested IIf
statements -- that results on the report with a single line
that has the parent names, separated by an ampersand if
there is more than one, e.g. : Smith, Mary & Smith, Joe
[here the student has two parents, both living in the
Primary Household, so that PHParentName1 and PHParentName2
are non-blank while OHParentName1 and OHParentName2 are
blank] OR Smith, Mary & Jones, Bill [here the student

has

a
mother listed as PHParentName1, a blank PHParent2, a blank
OHParent1 and father is OHParent2].

Can anybody offer any suggestion on how to get this
expression to take into account all the possibilities?

Thanks!
.

.

 

[Steve]

Thanks for a good start, but your solution only worked
correctly for cases in which PHParent1 and PHParent2 were
both not blank. It failed in all other cases in one way or
another.

Here are examples:

Case 1:

PHParentName1=Anderson, Paula
PHParentName2= (Null)
OHParentName1= (Null)
OHParentName2=Allen, Troy

Result using your forumula: Anderson, Paula &
[Here it doesn't pick up OHParent2]

Case 2:

PHParentName1=Gonzales, Barbara
PHParentName2= (Null)
OHParentName1= (Null)
OHParentName2= (Null)

Result using your forumula: Gonzales, Barbara &
[Here it adds an extraneous ampersand]

Case 3:

PHParentName1= (Null)
PHParentName2=Smith, Joe
OHParentName1=Jones, Mary
OHParentName2= (Null)

Result using your forumulaspace)& Smith, Joe
[Here there's an extraneous space and ampersand at the
beginning and no listing of OHParentName1

You appear to be running into some of the same problems as
me with the formula as it gets longer!

There must be a solution out there though! Thanks

-----Original Message-----
Sorry I found an error:
I think the following will give you a start:
= IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & " & IIf(([PHParentName2] Is Null
And [PHParentName1] Is Not Null) Or ([PHParentName1] Is
Null And [PHParentName2] Is Not Null),(IIf([OHParentName1]
Is Null,[OHParentName2],[OHParentName1])),[PHParentName2])
All of it on one line in a report or if you wish to put it
in a query:
Names: IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & " & IIf(([PHParentName2] Is Null
And [PHParentName1] Is Not Null) Or ([PHParentName1] Is
Null And [PHParentName2] Is Not Null),(IIf([OHParentName1]
Is Null,[OHParentName2],[OHParentName1])),[PHParentName2])
Again all on one line make sure you remove any > which are
added by this editor.
Hope this helps.
Fons

-----Original Message-----
I need help with a line on a report. I have a student
database and on the report I need to list the student's
parents'names. Each student lives with at least one
parent in a Primary Household. He may also have a second
parent in the primary household or a second parent living
in an Other Household.

The parents' names are coming from a query which has four
fields for the parent names: PHParentName1, PHParentName2,
OHParentName1, and OHParentName2. Up to three of these may
be blank (although either PHParentName1 or PHParentName2
will always be non-blank). I need an expression -- and I
have been trying to do it using a series of nested IIf
statements -- that results on the report with a single line
that has the parent names, separated by an ampersand if
there is more than one, e.g. : Smith, Mary & Smith, Joe
[here the student has two parents, both living in the
Primary Household, so that PHParentName1 and PHParentName2
are non-blank while OHParentName1 and OHParentName2 are
blank] OR Smith, Mary & Jones, Bill [here the student

has

a
mother listed as PHParentName1, a blank PHParent2, a blank
OHParent1 and father is OHParent2].

Can anybody offer any suggestion on how to get this
expression to take into account all the possibilities?

Thanks!
.

.

.

 

[Fons Ponsioen]

Apperently you did not use the update I had posted here,
But I also resolved the issue with the trailing lonely
ampersand.
Try this
IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & "+IIf(([PHParentName2] Is Null And
[PHParentName1] Is Not Null) Or ([PHParentName1] Is Null
And [PHParentName2] Is Not Null),(IIf([OHParentName1] Is
Null,[OHParentName2],[OHParentName1])),[PHParentName2])
The ampersand is resolved by using the + instead of the &.
Now, the only case this formula will not record is if bot
OH are filled and no PH.
Hope this helps.
Fons

-----Original Message-----
Thanks for a good start, but your solution only worked
correctly for cases in which PHParent1 and PHParent2 were
both not blank. It failed in all other cases in one way or
another.

Here are examples:

Case 1:

PHParentName1=Anderson, Paula
PHParentName2= (Null)
OHParentName1= (Null)
OHParentName2=Allen, Troy

Result using your forumula: Anderson, Paula &
[Here it doesn't pick up OHParent2]

Case 2:

PHParentName1=Gonzales, Barbara
PHParentName2= (Null)
OHParentName1= (Null)
OHParentName2= (Null)

Result using your forumula: Gonzales, Barbara &
[Here it adds an extraneous ampersand]

Case 3:

PHParentName1= (Null)
PHParentName2=Smith, Joe
OHParentName1=Jones, Mary
OHParentName2= (Null)

Result using your forumulaspace)& Smith, Joe
[Here there's an extraneous space and ampersand at the
beginning and no listing of OHParentName1

You appear to be running into some of the same problems as
me with the formula as it gets longer!

There must be a solution out there though! Thanks

-----Original Message-----
Sorry I found an error:
I think the following will give you a start:
= IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & " & IIf(([PHParentName2] Is Null
And [PHParentName1] Is Not Null) Or ([PHParentName1] Is
Null And [PHParentName2] Is Not Null),(IIf ([OHParentName1]
Is Null,[OHParentName2],[OHParentName1])), [PHParentName2])
All of it on one line in a report or if you wish to put it
in a query:
Names: IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & " & IIf(([PHParentName2] Is Null
And [PHParentName1] Is Not Null) Or ([PHParentName1] Is
Null And [PHParentName2] Is Not Null),(IIf ([OHParentName1]
Is Null,[OHParentName2],[OHParentName1])), [PHParentName2])
Again all on one line make sure you remove any > which are
added by this editor.
Hope this helps.
Fons

-----Original Message-----
I need help with a line on a report. I have a student
database and on the report I need to list the student's
parents'names. Each student lives with at least one
parent in a Primary Household. He may also have a second
parent in the primary household or a second parent living
in an Other Household.

The parents' names are coming from a query which has four
fields for the parent names: PHParentName1,
PHParentName2,
OHParentName1, and OHParentName2. Up to three of these
may
be blank (although either PHParentName1 or PHParentName2
will always be non-blank). I need an expression -- and
I
have been trying to do it using a series of nested IIf
statements -- that results on the report with a single
line
that has the parent names, separated by an ampersand if
there is more than one, e.g. : Smith, Mary & Smith, Joe
[here the student has two parents, both living in the
Primary Household, so that PHParentName1 and
PHParentName2
are non-blank while OHParentName1 and OHParentName2 are
blank] OR Smith, Mary & Jones, Bill [here the student has
a
mother listed as PHParentName1, a blank PHParent2, a
blank
OHParent1 and father is OHParent2].

Can anybody offer any suggestion on how to get this
expression to take into account all the possibilities?

Thanks!
.

.

.

.

 

[Fons Ponsioen]

After reviewing the posting below this one, here is the
complete solution:
NamesTot: [PHParentName1] & (IIf([PHParentName1] Is
Null,""," & ")+[PHParentName2]) & (IIf([PHParentName1] Is
Null And [PHParentName2] Is Null,""," & ")+
[OHParentName1]) & (" & "+[OHParentName2])
Hope this finishes it all.
enjoyed the challenge.
Fons

 

[Steve]

Well, apparently this is as tough as I thought! I'm afraid
your last suggestion:

ParentNames: IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & "+IIf(([PHParentName2] Is Null And
[PHParentName1] Is Not Null) Or ([PHParentName1] Is Null
And [PHParentName2] Is Not Null),(IIf([OHParentName1] Is
Null,[OHParentName2],[OHParentName1])),[PHParentName2])

does not work either; it does not pick up ANY names from
either OHParent1 or OHParent 2, plus it leaves a hanging
"&" when there is only a PHParent1 and puts in a blank
leading space if there is no PHParent1 but there is a
PHParent2. In fact, the results are exactly the same as
listed in my reply above So, if you're up for more
challenge.....have at it!!

Thanks

 

[Steve]

OOoops!

I finally found the problem: fields that I was calling Null
were the results of another expression and it turns out
they were BLANK but not null. When I corrected the formula
so that they were truly NULL, your expression worked
perfectly!

Thanks so much )

-----Original Message-----
Well, apparently this is as tough as I thought! I'm afraid
your last suggestion:

ParentNames: IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & "+IIf(([PHParentName2] Is Null And
[PHParentName1] Is Not Null) Or ([PHParentName1] Is Null
And [PHParentName2] Is Not Null),(IIf([OHParentName1] Is
Null,[OHParentName2],[OHParentName1])),[PHParentName2])

does not work either; it does not pick up ANY names from
either OHParent1 or OHParent 2, plus it leaves a hanging
"&" when there is only a PHParent1 and puts in a blank
leading space if there is no PHParent1 but there is a
PHParent2. In fact, the results are exactly the same as
listed in my reply above So, if you're up for more
challenge.....have at it!!

Thanks

-----Original Message-----
After reviewing the posting below this one, here is the
complete solution:
NamesTot: [PHParentName1] & (IIf([PHParentName1] Is
Null,""," & ")+[PHParentName2]) & (IIf([PHParentName1] Is
Null And [PHParentName2] Is Null,""," & ")+
[OHParentName1]) & (" & "+[OHParentName2])
Hope this finishes it all.
enjoyed the challenge.
Fons
.

.

 

[Fons Ponsioen]

Glad it did work, since I tried it here with some fate
data and it all worked out well.
Yes, Null and "" is not the same.
Take care.
Fons

-----Original Message-----
OOoops!

I finally found the problem: fields that I was calling Null
were the results of another expression and it turns out
they were BLANK but not null. When I corrected the formula
so that they were truly NULL, your expression worked
perfectly!

Thanks so much )

-----Original Message-----
Well, apparently this is as tough as I thought! I'm afraid
your last suggestion:

ParentNames: IIf([PHParentName1] Is Null,[PHParentName2],
[PHParentName1]) & " & "+IIf(([PHParentName2] Is Null And
[PHParentName1] Is Not Null) Or ([PHParentName1] Is Null
And [PHParentName2] Is Not Null),(IIf([OHParentName1] Is
Null,[OHParentName2],[OHParentName1])),[PHParentName2])

does not work either; it does not pick up ANY names from
either OHParent1 or OHParent 2, plus it leaves a hanging
"&" when there is only a PHParent1 and puts in a blank
leading space if there is no PHParent1 but there is a
PHParent2. In fact, the results are exactly the same as
listed in my reply above So, if you're up for more
challenge.....have at it!!

Thanks

-----Original Message-----
After reviewing the posting below this one, here is the
complete solution:
NamesTot: [PHParentName1] & (IIf([PHParentName1] Is
Null,""," & ")+[PHParentName2]) & (IIf([PHParentName1] Is
Null And [PHParentName2] Is Null,""," & ")+
[OHParentName1]) & (" & "+[OHParentName2])
Hope this finishes it all.
enjoyed the challenge.
Fons
.

.

.