How to code this conditional...

[Guest]

Hello,
I am trying to keep track of what trainings employees have had, as well as
their expiry dates. The problem is that only 4 classes have expiry dates, the
other 12 (and growing) do not.
I would like to include a statement in a field that would show when the
classes that have expiry dates expire, and leave blank the classes that do
not.
For example, First Aid expires after 3 years, CPR expires after a year. I
used the IIF statement =DateAdd("yyyy",IIf([Class Name]='CPR',1,3),[Class
Date]) but this adds 3 years to every class, even to the ones that have no
expiry dates.
Is there a better way to do this other than working on nesting a very long
IIF?
Almost pulling my hair out,
John.

 

[tina]

do you have a table which lists all classes? if so, suggest you add a field
to the table, i'll call it Term, with data type Number (size as Byte). i'll
assume the terms are all years - as in 1 year, 3 years, etc.
for a form or report, link the Classes table to your data table in the
underlying query, and create the following calculated field, as

Expiration: IIf([Term] Is Null, Null, DateAdd("yyyy",[Term],[Class
Date])

hth

 

[Guest]

Hey Tina,
Thank you! I did have a class interval field that showed how long the class
took. I used the IIf statement and yes, it worked.

Many thanks, and have a Happy New Year!

John.

do you have a table which lists all classes? if so, suggest you add a field
to the table, i'll call it Term, with data type Number (size as Byte). i'll
assume the terms are all years - as in 1 year, 3 years, etc.
for a form or report, link the Classes table to your data table in the
underlying query, and create the following calculated field, as

Expiration: IIf([Term] Is Null, Null, DateAdd("yyyy",[Term],[Class
Date])

hth

Hello,
I am trying to keep track of what trainings employees have had, as well as
their expiry dates. The problem is that only 4 classes have expiry dates, the
other 12 (and growing) do not.
I would like to include a statement in a field that would show when the
classes that have expiry dates expire, and leave blank the classes that do
not.
For example, First Aid expires after 3 years, CPR expires after a year. I
used the IIF statement =DateAdd("yyyy",IIf([Class Name]='CPR',1,3),[Class
Date]) but this adds 3 years to every class, even to the ones that have no
expiry dates.
Is there a better way to do this other than working on nesting a very long
IIF?
Almost pulling my hair out,
John.

 

[tina]

you're welcome, Happy Holidays!

Hey Tina,
Thank you! I did have a class interval field that showed how long the class
took. I used the IIf statement and yes, it worked.

Many thanks, and have a Happy New Year!

John.

do you have a table which lists all classes? if so, suggest you add a field
to the table, i'll call it Term, with data type Number (size as Byte). i'll
assume the terms are all years - as in 1 year, 3 years, etc.
for a form or report, link the Classes table to your data table in the
underlying query, and create the following calculated field, as

Expiration: IIf([Term] Is Null, Null, DateAdd("yyyy",[Term],[Class
Date])

hth

Hello,
I am trying to keep track of what trainings employees have had, as well as
their expiry dates. The problem is that only 4 classes have expiry

dates,
the

other 12 (and growing) do not.
I would like to include a statement in a field that would show when the
classes that have expiry dates expire, and leave blank the classes that do
not.
For example, First Aid expires after 3 years, CPR expires after a year. I
used the IIF statement =DateAdd("yyyy",IIf([Class Name]='CPR',1,3),[Class
Date]) but this adds 3 years to every class, even to the ones that

have
no

expiry dates.
Is there a better way to do this other than working on nesting a very long
IIF?
Almost pulling my hair out,
John.