IIf statement

[Marge]

I have 5 fields to accommodate promotion dates (i.e., promodate1, promodate2,
etc.) and a hire date. I need to produce a list based on the most recent
promotion date or, if no promotions have been made for an employee, their
hire date. So if promodate5 is blank, I should be shown promodate4, if that
is blank, show me promodate3, and so on. I don't know how to tell the query
what I need. Any help is greatly appreciated.

 

[Dale Fye]

Well,

My first recommendation would be to get rid of your individual promotion
date fields and create an EmployeePromotions table, that contains EmployeeID,
PromotionDate, and whatever other fields are pertinent to promotions.

Having said that, you could us nested IIF( ) functions, or the Switch
function, but I think the easiest way to get what you are looking for is
something like:

ComputedField:NZ(promodate5, NZ(promodate4, NZ(promodate3, NZ(promodate2,
NZ(Promodate1, HireDate)))))

Although I do have a function I use in cases like this

ComputedField: fnMax(hiredate, promodate1, promodate2, promodate3, ...)

where fnMax looks like:

Public Function fnMax(ParamArray ValList() As Variant) As Variant

Dim intLoop As Integer
Dim myVal As Variant

For intLoop = LBound(ValList) To UBound(ValList)
If Not IsNull(ValList(intLoop)) Then
If IsEmpty(myVal) Then
myVal = ValList(intLoop)
ElseIf ValList(intLoop) > myVal Then
myVal = ValList(intLoop)
End If
End If
Next
fnMax = myVal

End Function


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Marge]

Dale -- I'm totally clueless about why the NZ solution worked, but it did.
Thanks for your help! -- Marge

 

[Dale Fye]

Marge,

The NZ( ) function allows two arguments. The first argument is the field
that you want to evaluate. If that field is not null, then it returns that
field, if it is null, then it returns the second argument.

So, the way I would read the statement I wrote out for you is:

IF NOT ISNULL([promodate5]) Then
ComputedField = [Promodate5]
ELSEIF NOT isnull([Promodate4]) then
computedField = [PromoDate4]
elseif NOT ISNULL([promodate3]) then
computedField = [Promodate3]
elseif NOT ISNULL([Promodate2]) then
computedField = [Promodate2]
elseif NOT ISNULL([Promodate1]) then
computedField = [Promodate1]
ELSE
computedField= [HireDate]
endif


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Marge]

I'm one of those who isn't satisfied just doing something; I need to know why
I'm doing it to make sense of it. You've made me very happy! Thanks again.
Life goes on!

Marge,

The NZ( ) function allows two arguments. The first argument is the field
that you want to evaluate. If that field is not null, then it returns that
field, if it is null, then it returns the second argument.

So, the way I would read the statement I wrote out for you is:

IF NOT ISNULL([promodate5]) Then
ComputedField = [Promodate5]
ELSEIF NOT isnull([Promodate4]) then
computedField = [PromoDate4]
elseif NOT ISNULL([promodate3]) then
computedField = [Promodate3]
elseif NOT ISNULL([Promodate2]) then
computedField = [Promodate2]
elseif NOT ISNULL([Promodate1]) then
computedField = [Promodate1]
ELSE
computedField= [HireDate]
endif


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale -- I'm totally clueless about why the NZ solution worked, but it did.
Thanks for your help! -- Marge