Expression help - Nested IIf's??

  • Thread starter Thread starter Steve
  • Start date Start date
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!
 
Sorry about weird extraneous characters (I pasted from Word
document) Please see Corrected Version posted after this
one)
 
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!

.
Click to expand...
.
Click to expand...
 
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!

.
Click to expand...
.
Click to expand...
Click to expand...
 
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 --
Click to expand...
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!

.

.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top