Working with IIf statements

[David Mulholland]

I'm working on a query that bouncing dates against one another. I think it
needs nested IIf statements, but my brain just can't seem to put it all
together. I'd like it to work in a single query to keep things trim and
because I'll need to modify it for 8 different cases.

Last_Promotion (already captured)
Eligibility_date (calculated field - Last_Promotion + 4 months)
Cutoff (will be criteria)
Hire
Promotion

I need to come up with something that says:

[Promotion] = [Eligibility_date]

If [Hire] is populated, then [Promotion] = [Hire] +1 day

If [Hire] is null, [Promotion] = [Eligibility_date] or ([Cutoff] - 4 months)
whichever is greater.

I've seem some good stuff here and what you guys can come up with. I've used
knowledge from here and it's really made my job easier. Hope you guys don't
disappoint. Pre-thanks to all!!

 

[John Spencer]

It's a bit unclear to me what you want and what you have
Are these all fields? Are they all Date fields?
Last_Promotion (Date field that is alreay populated)

Hire (Date Field that is already populated?)

Promotion (Date field? What is the relation between Promotion and
Last_Promotion? Is Promotion the next expected promote date?)

Eligibility_Date

Cutoff

The expression you are looking for might be


IIF(Hire is not null, Hire + 1,
IIF(Eligibility_date > DateAdd("m",-4,CutOff),
Eligibility_Date, DateAdd("m",-4,CutOff))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================