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!

 

[Steve]

Sorry about weird extraneous characters (I pasted from Word
document) Please see Corrected Version posted after this
one)

 

[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!
..

-----Original Message-----
Sorry about weird extraneous characters (I pasted from Word
document) Please see Corrected Version posted after this
one)

-----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!

.

.

 

[John Spencer (MVP)]

Try using the following variation. Note that I am using two different
concatenation operators.

PhParentName1 &
IIF(PhParentName1 is Null,PhParentName2, " & " + PhParentName2) &
(" & " + OHParentName1) &
(" & " + OHParentName2)

When concatenating text with the + nulls (blanks) propagate; with & the nulls
are treated as if they are zero-length strings.

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!
.

-----Original Message-----
Sorry about weird extraneous characters (I pasted from Word
document) Please see Corrected Version posted after this
one)

-----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 your reply. I like the elegance of your solution
and did not know about the "+" version of concantenation.
Unfortunately, your solution ends up with extraneous
ampersands in all cases where a field is blank.

Here are a couple of examples:

Case 1:

PHParentName1=Wall, Katherine
PHParentName2=Adkins, Eric
OHParentName1= (Null)
OHParentName2= (Null)

Expression Result: Wall, Katherine & Adkins, Eric & &

Case 2:

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

Expression Result: Anderson, Paula & & & Allen, Troy

Case 3:

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

Expression Result: Gonzales, Barbara & & &

Any ideas on how to get rid of the unwanted ampersands?

Thanks!

-----Original Message-----
Try using the following variation. Note that I am using two different
concatenation operators.

PhParentName1 &
IIF(PhParentName1 is Null,PhParentName2, " & " + PhParentName2) &
(" & " + OHParentName1) &
(" & " + OHParentName2)

When concatenating text with the + nulls (blanks) propagate; with & the nulls
are treated as if they are zero-length strings.

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!
.

-----Original Message-----
Sorry about weird extraneous characters (I pasted from Word
document) Please see Corrected Version posted after this
one)

-----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!

.

.

.