How to code simple if statements?

[Guest]

Hello,
I have a training database that keeps track of what trainings employees have
had.
Some trainings have expiry dates, eg. CPR expires after a year, First Aid
after 3 years, etc. Some training classes like "Communication" do not expire.
On my form, I have a field called Class Expires. I have the formula:
=dateadd("yyyy",3,[Class Date])
As you can see, it adds 3 years to every class date.
I would like to set in up in such a way that if CPR is chosen in class name,
once the class date is entered, it adds a year then displays the expiry date
for CPR in the Expires field. If First Aid is chosen, it adds 3 years. If a
class that doesn't have an expiry is chosen, the field remains blank.
My class names field is a combo box that looks up its values from my class
table.

Any help would be greatly appreciated!

 

[Ken Snell [MVP]]

Here is a generic start for you to work from:

=DateAdd("yyyy", IIf([Class Name]='CPR', 1, [Class Name]='First Aid',
3,0)),[Class Date])

 

[Mike Painter]

Hello,
I have a training database that keeps track of what trainings
employees have had.
Some trainings have expiry dates, eg. CPR expires after a year, First
Aid after 3 years, etc. Some training classes like "Communication" do
not expire. On my form, I have a field called Class Expires. I have
the formula: =dateadd("yyyy",3,[Class Date])
As you can see, it adds 3 years to every class date.
I would like to set in up in such a way that if CPR is chosen in
class name, once the class date is entered, it adds a year then
displays the expiry date for CPR in the Expires field. If First Aid
is chosen, it adds 3 years. If a class that doesn't have an expiry is
chosen, the field remains blank.
My class names field is a combo box that looks up its values from my
class table.

Any help would be greatly appreciated!

Add a table with Class names, expiration dates and possiblly agencies and
description.
Class Agency Expires
CPR ARC 1
CPR AHA 2
Comm XXX 99
Join that table with your training table and use either
=dateadd("yyyy",[expires],[Class Date])
or = iif( Expires = 99, "Never" ,dateadd("yyyy",3,[Class Date])

 

[Guest]

Hey Ken,

When I type in what you wrote, the error says: " The expression you entered
has a function containing the wrong number of arguments."
I noticed we have 5 parentheses in the statement. Shouldn't that be an even
number?

Thanks,

John.

Here is a generic start for you to work from:

=DateAdd("yyyy", IIf([Class Name]='CPR', 1, [Class Name]='First Aid',
3,0)),[Class Date])

--

Ken Snell
<MS ACCESS MVP>

Hello,
I have a training database that keeps track of what trainings employees
have
had.
Some trainings have expiry dates, eg. CPR expires after a year, First Aid
after 3 years, etc. Some training classes like "Communication" do not
expire.
On my form, I have a field called Class Expires. I have the formula:
=dateadd("yyyy",3,[Class Date])
As you can see, it adds 3 years to every class date.
I would like to set in up in such a way that if CPR is chosen in class
name,
once the class date is entered, it adds a year then displays the expiry
date
for CPR in the Expires field. If First Aid is chosen, it adds 3 years. If
a
class that doesn't have an expiry is chosen, the field remains blank.
My class names field is a combo box that looks up its values from my class
table.

Any help would be greatly appreciated!

 

[Ken Snell [MVP]]

Sorry.... my typo:


=DateAdd("yyyy", IIf([Class Name]='CPR', 1, IIf([Class Name]='First Aid',

3,0)),[Class Date])

--

Ken Snell
<MS ACCESS MVP>

Hey Ken,

When I type in what you wrote, the error says: " The expression you
entered
has a function containing the wrong number of arguments."
I noticed we have 5 parentheses in the statement. Shouldn't that be an
even
number?

Thanks,

John.

Here is a generic start for you to work from:

=DateAdd("yyyy", IIf([Class Name]='CPR', 1, [Class Name]='First Aid',
3,0)),[Class Date])

--

Ken Snell
<MS ACCESS MVP>

Hello,
I have a training database that keeps track of what trainings employees
have
had.
Some trainings have expiry dates, eg. CPR expires after a year, First
Aid
after 3 years, etc. Some training classes like "Communication" do not
expire.
On my form, I have a field called Class Expires. I have the formula:
=dateadd("yyyy",3,[Class Date])
As you can see, it adds 3 years to every class date.
I would like to set in up in such a way that if CPR is chosen in class
name,
once the class date is entered, it adds a year then displays the expiry
date
for CPR in the Expires field. If First Aid is chosen, it adds 3 years.
If
a
class that doesn't have an expiry is chosen, the field remains blank.
My class names field is a combo box that looks up its values from my
class
table.

Any help would be greatly appreciated!

 

[Guest]

Hey Ken,

Now it's saying the expression I entered (the one you sent) has an invalid
string (string can be 2048 chars long, including quote marks)
The expiry field comes from the table with type date/time.
That's a bummer, because I have two other classes that have long names that
I also need to include in the IIF statement:
CPR - 1 year
First Aid - 3 years
Article 9 - 3 years
Client Intervention Training I - 3 years
Client Intervention Training II - 3 years

The other classes don't have expiry dates. Help!

John.

Sorry.... my typo:


=DateAdd("yyyy", IIf([Class Name]='CPR', 1, IIf([Class Name]='First Aid',

3,0)),[Class Date])

--

Ken Snell
<MS ACCESS MVP>

Hey Ken,

When I type in what you wrote, the error says: " The expression you
entered
has a function containing the wrong number of arguments."
I noticed we have 5 parentheses in the statement. Shouldn't that be an
even
number?

Thanks,

John.

Here is a generic start for you to work from:

=DateAdd("yyyy", IIf([Class Name]='CPR', 1, [Class Name]='First Aid',
3,0)),[Class Date])

--

Ken Snell
<MS ACCESS MVP>

Hello,
I have a training database that keeps track of what trainings employees
have
had.
Some trainings have expiry dates, eg. CPR expires after a year, First
Aid
after 3 years, etc. Some training classes like "Communication" do not
expire.
On my form, I have a field called Class Expires. I have the formula:
=dateadd("yyyy",3,[Class Date])
As you can see, it adds 3 years to every class date.
I would like to set in up in such a way that if CPR is chosen in class
name,
once the class date is entered, it adds a year then displays the expiry
date
for CPR in the Expires field. If First Aid is chosen, it adds 3 years.
If
a
class that doesn't have an expiry is chosen, the field remains blank.
My class names field is a combo box that looks up its values from my
class
table.

Any help would be greatly appreciated!

 

[John Vinson]

Now it's saying the expression I entered (the one you sent) has an invalid
string (string can be 2048 chars long, including quote marks)
The expiry field comes from the table with type date/time.
That's a bummer, because I have two other classes that have long names that
I also need to include in the IIF statement:
CPR - 1 year
First Aid - 3 years
Article 9 - 3 years
Client Intervention Training I - 3 years
Client Intervention Training II - 3 years

The "too long" message often occurs if you accidentally leave off a
closing quote. Doublecheck your expression!

Since CPR is the only training which has a one-year value, you could
just use

DateAdd("yyyy", IIF([Class Name] = "CPR", 1, 3))

However, a more general solution would be to have a Table of classes
(with five rows, so far; you may eventually need more) with fields for
class name and for the time interval. You could then just join this
table in a Query to pick up the interval, with no IIF and no code at
all.


John W. Vinson[MVP]