Nested IIF question

[Guest]

We have a database for tracking clients through a series of classes. There
may be 4, 5, or 6 classes in a unit. We want to do a report that shows the
LAST class date in the unit. I suppose I should use a nested IIF statement,
but I'm not sure how to do it. I want to say that if Class 6 date is null,
print the date of Class 5, but if Class 5 date is also null, print the date
of Class 4. I get as far as:

=IIF([Clas6] Is null, [Class5], IIf([Class5] Is null, [Class4],???))

I'm not sure what to put in for the ??? Is there a better way to show the
LAST date that is not null? Thanks in advance!

 

[Guest]

You would continue until you have tested all but the last and use last if
next to last is null.
Like this --
IIf([Class3] Is null, [Class2],[Class1])))))

But you really need to revise your database structure so that a single
record has these multiple dates. You should have a child table with records
of the dates.

 

[Douglas J. Steele]

You can't use Is Null in IIf statements: it's only for SQL.

You could use either:

IIf(IsNull([Class6]), IIf(IsNull([Class5], [Class4], [Class5]), [Class6])

or

Nz([Class6], Nz([Class5], [Class4]))

 

[Jerry Porter]

Douglas,

If IIF is used in a query, you can use Is Null. But not when it's used
in code.

Jerry

 

[Guest]

Douglas - Using your first example I got the error message of an extra ) in
the expression. Using the Nz example gave no results.

Karl - Using your example I got the error message of an comma error which I
couldn't find. And I'm not sure how you mean I should structure the
database. We have a series of groups with 4 to 6 class dates per group.
There is a group form which has, among other things, fields in which to
indicate the dates on which the classes are to be held. When we run a
specific report, we just want the last class date to show, whether it is
class #4, 5, or 6 (in other words, the last date the group met). We run many
groups throughout the year, each group having from 4 to 6 classes depending

 

[Douglas J. Steele]

Sorry: typo

IIf(IsNull([Class6]), IIf(IsNull([Class5]), [Class4], [Class5]), [Class6])

The Nz should definitely have given something, unless Class4 was null. What
happens if you try

Nz([Class6], Nz([Class5], Nz([Class4], 0))

?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas - Using your first example I got the error message of an extra )
in
the expression. Using the Nz example gave no results.

Karl - Using your example I got the error message of an comma error which
I
couldn't find. And I'm not sure how you mean I should structure the
database. We have a series of groups with 4 to 6 class dates per group.
There is a group form which has, among other things, fields in which to
indicate the dates on which the classes are to be held. When we run a
specific report, we just want the last class date to show, whether it is
class #4, 5, or 6 (in other words, the last date the group met). We run
many
groups throughout the year, each group having from 4 to 6 classes
depending

We have a database for tracking clients through a series of classes.
There
may be 4, 5, or 6 classes in a unit. We want to do a report that shows
the
LAST class date in the unit. I suppose I should use a nested IIF
statement,
but I'm not sure how to do it. I want to say that if Class 6 date is
null,
print the date of Class 5, but if Class 5 date is also null, print the
date
of Class 4. I get as far as:

=IIF([Clas6] Is null, [Class5], IIf([Class5] Is null, [Class4],???))

I'm not sure what to put in for the ??? Is there a better way to show
the
LAST date that is not null? Thanks in advance!

 

[Guest]

Post you complete SQL.

You would have a Group table with GroupID as primary key and a Class table
with these fields.
ClassID
ClassName
ClassDate
GroupID

You would set a one-to-many relationship between the group and class table.

Douglas - Using your first example I got the error message of an extra ) in
the expression. Using the Nz example gave no results.

Karl - Using your example I got the error message of an comma error which I
couldn't find. And I'm not sure how you mean I should structure the
database. We have a series of groups with 4 to 6 class dates per group.
There is a group form which has, among other things, fields in which to
indicate the dates on which the classes are to be held. When we run a
specific report, we just want the last class date to show, whether it is
class #4, 5, or 6 (in other words, the last date the group met). We run many
groups throughout the year, each group having from 4 to 6 classes depending

We have a database for tracking clients through a series of classes. There
may be 4, 5, or 6 classes in a unit. We want to do a report that shows the
LAST class date in the unit. I suppose I should use a nested IIF statement,
but I'm not sure how to do it. I want to say that if Class 6 date is null,
print the date of Class 5, but if Class 5 date is also null, print the date
of Class 4. I get as far as:

=IIF([Clas6] Is null, [Class5], IIf([Class5] Is null, [Class4],???))

I'm not sure what to put in for the ??? Is there a better way to show the
LAST date that is not null? Thanks in advance!

 

[Guest]

Douglas - I still get a ) error on the first example, and now I get a comma
error on the Nz example. Any more ideas?

Sorry: typo

IIf(IsNull([Class6]), IIf(IsNull([Class5]), [Class4], [Class5]), [Class6])

The Nz should definitely have given something, unless Class4 was null. What
happens if you try

Nz([Class6], Nz([Class5], Nz([Class4], 0))

?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas - Using your first example I got the error message of an extra )
in
the expression. Using the Nz example gave no results.

Karl - Using your example I got the error message of an comma error which
I
couldn't find. And I'm not sure how you mean I should structure the
database. We have a series of groups with 4 to 6 class dates per group.
There is a group form which has, among other things, fields in which to
indicate the dates on which the classes are to be held. When we run a
specific report, we just want the last class date to show, whether it is
class #4, 5, or 6 (in other words, the last date the group met). We run
many
groups throughout the year, each group having from 4 to 6 classes
depending

We have a database for tracking clients through a series of classes.
There
may be 4, 5, or 6 classes in a unit. We want to do a report that shows
the
LAST class date in the unit. I suppose I should use a nested IIF
statement,
but I'm not sure how to do it. I want to say that if Class 6 date is
null,
print the date of Class 5, but if Class 5 date is also null, print the
date
of Class 4. I get as far as:

=IIF([Clas6] Is null, [Class5], IIf([Class5] Is null, [Class4],???))

I'm not sure what to put in for the ??? Is there a better way to show
the
LAST date that is not null? Thanks in advance!

 

[Douglas J. Steele]

The IIf is definitely correct as I've posted it.

The Nz is missing a terminating ) (again, my apologies):

Nz([Class6], Nz([Class5], Nz([Class4], 0)))


Where are you using this? If it's in VBA, show the code please.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas - I still get a ) error on the first example, and now I get a
comma
error on the Nz example. Any more ideas?

Sorry: typo

IIf(IsNull([Class6]), IIf(IsNull([Class5]), [Class4], [Class5]),
[Class6])

The Nz should definitely have given something, unless Class4 was null.
What
happens if you try

Nz([Class6], Nz([Class5], Nz([Class4], 0))

?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas - Using your first example I got the error message of an
extra )
in
the expression. Using the Nz example gave no results.

Karl - Using your example I got the error message of an comma error
which
I
couldn't find. And I'm not sure how you mean I should structure the
database. We have a series of groups with 4 to 6 class dates per
group.
There is a group form which has, among other things, fields in which to
indicate the dates on which the classes are to be held. When we run a
specific report, we just want the last class date to show, whether it
is
class #4, 5, or 6 (in other words, the last date the group met). We
run
many
groups throughout the year, each group having from 4 to 6 classes
depending
on the nature of the group. I hope this helps.

:

We have a database for tracking clients through a series of classes.
There
may be 4, 5, or 6 classes in a unit. We want to do a report that
shows
the
LAST class date in the unit. I suppose I should use a nested IIF
statement,
but I'm not sure how to do it. I want to say that if Class 6 date is
null,
print the date of Class 5, but if Class 5 date is also null, print the
date
of Class 4. I get as far as:

=IIF([Clas6] Is null, [Class5], IIf([Class5] Is null, [Class4],???))

I'm not sure what to put in for the ??? Is there a better way to show
the
LAST date that is not null? Thanks in advance!