Multiple IIF expression in Query

[Guest]

I have taught myself access after a few very, very basic classes. I have
created a Training Database to track required training hours for multiple
departments and multiple levels of training. My problem is a nested IIF
statement. I have the following expression in my query to tell me if the
training has been complete in the first 90 days.

Training Taken at Hire: IIf([Applicable?]=-1 And [Date_Comp]=0,"Need
Initial",IIf([Date_Comp]-[TrainingHireDate]<=90,"YES"," ")) I get yes and
blank returned but not need initial.
But what I really want my query to return is
1. "Yes" if training is applicable and has been taken in the first 90
days after hire.
2. "Need Initial" if it is applicable but has not been taken
3. "NA" if it is not applicable
4. " "or "NO" if the training is taken after 90 days.

If I try to add another IIF statement I get a message that I have too many
arguments. The IIF statement above will give me a Yes and the rest of the
fields are blank. It doesn't yield "Need Initial". I have several different
variations but with the same or similar results. Can any one help see what
is wrong? Other fields that I have tried to use in my query are New
Hire?-tells me if they are in their 1st year of employment, Hire frequency
(how many days that the employee has to take the training) and Refresher
frequency (how often the employee must take the refresher). Most training
must be completed in the first 90 days and retaken as a refresher every year.

 

[Randy Harris]

I have taught myself access after a few very, very basic classes. I have
created a Training Database to track required training hours for multiple
departments and multiple levels of training. My problem is a nested IIF
statement. I have the following expression in my query to tell me if the
training has been complete in the first 90 days.

Training Taken at Hire: IIf([Applicable?]=-1 And [Date_Comp]=0,"Need
Initial",IIf([Date_Comp]-[TrainingHireDate]<=90,"YES"," ")) I get yes and
blank returned but not need initial.
But what I really want my query to return is
1. "Yes" if training is applicable and has been taken in the first 90
days after hire.
2. "Need Initial" if it is applicable but has not been taken
3. "NA" if it is not applicable
4. " "or "NO" if the training is taken after 90 days.

If I try to add another IIF statement I get a message that I have too many
arguments. The IIF statement above will give me a Yes and the rest of the
fields are blank. It doesn't yield "Need Initial". I have several different
variations but with the same or similar results. Can any one help see what
is wrong? Other fields that I have tried to use in my query are New
Hire?-tells me if they are in their 1st year of employment, Hire frequency
(how many days that the employee has to take the training) and Refresher
frequency (how often the employee must take the refresher). Most training
must be completed in the first 90 days and retaken as a refresher every year.

Pat, just a total shot in the dark here, but if Date_Comp is a date field,
it will never be equal to 0.

 

[Guest]

SELECT [Training-Hire].TrainingHireDate, [Training-Hire].Date_Comp,
[Training-Hire].[Applicable?], IIf([Applicable?]=0,"NA",IIf([Date_Comp] Is
Null,"Need Initial",IIf([Date_Comp]-[TrainingHireDate]<=90,"YES","NO"))) AS
[Training Taken at Hire]
FROM [Training-Hire];

 

[PC Datasheet]

Look at the Switch function in the Help file. It's probably more applicable
than the Intermediate If Function.