Nested IIF - more than one conditional....can't do it!!!

[Chey]

I am trying to have an expression that displays for all current contracts the
word
Current and for expired the word expired. But for the current ones I also
need to consider the Cont. (continuous contract, never expires). I have the
field set as a text due to the Cont. agreements. Please help me with the
formula. Tks. I tried the one below but did not work....

Expr1: IIf((DateValue([CONTRACT END DATE])>=Now() Or ([CONTRACT END
DATE])="CONT."),"Current","Expired")

 

[BruceM]

Try using Date() rather than Now()

 

[bcap]

Ugh. You can't use the DateValue function on a field containing non-date
values, which means that this cannot be made to work other than by writing a
VBA function to do it. You can't even wrap it in another Iif to test first
whether it's a date, because both parts will still get evaluated so you'll
still get an error.

The real problem, however, is your design. Trying to force dates and text
into the same field like this is going to cause you endless problems.
What's wrong with having a date field to store the end date (or Null for
continuous contracts) and a separate field (e.g. a Yes/No field, at it's
simplest) which indicates whether a contract is continuous?

 

[Klatuu]

Are you sure that is what you want?
If the contract end date is >= now, that would mean the contract date has
already passed and should indicate the contract has ended.

First, I doubt you should be using Now. Now contains a specific time
component and may cause some date comparisons and calculations to return
results different from those you expect. For example, the DateValue
function only returns the date part. It does not return the specific time.

Now, since you are carrying the date as text, you need to be sure the format
you are using can be converted by the DateValue function. I would suggest
you review the VBA Help topic on the DateValue function. Then, to simplify
your IIf, all you need to do is check for Expired first because everything
else is deemed current:


It probably needs to be:
Expr1: IIf((DateValue([CONTRACT END DATE])>=Date(), "Expired", "Current")

 

[Chey]

For the now() it is working. The problem is when I tried to also consider
the Cont.

Try using Date() rather than Now()

I am trying to have an expression that displays for all current contracts
the
word
Current and for expired the word expired. But for the current ones I also
need to consider the Cont. (continuous contract, never expires). I have
the
field set as a text due to the Cont. agreements. Please help me with the
formula. Tks. I tried the one below but did not work....

Expr1: IIf((DateValue([CONTRACT END DATE])>=Now() Or ([CONTRACT END
DATE])="CONT."),"Current","Expired")

 

[Ken Sheridan]

If the value in the column is "Cont." then calling the DateValue function
will raise an error as it can't be evaluated as a date. Similarly if the
column is Null. Another point is that by calling the Now() function rather
than the Date() function contracts with an end date of today will return
'expired' rather than 'current' as the Now() function returns the current
date and time, so any values without a time will be < Now() unless it is run
exactly on the stoke of midnight at the start of the day. This is because a
date without a time is in fact a date/time value at midnight at the start of
the day.

You could expand the expression with further nested IIf function calls to
handle any Null or 'cont.' columns, but rather than using an expression add
the following function to a standard module:

Public Function ContractStatus(varDate As Variant) As Variant

ContractStatus = Null

If Not IsNull(varDate) Then
Select Case varDate
Case "Cont."
ContractStatus = "Current"
Case Else
If CDate(varDate) >= VBA.Date Then
ContractStatus = "Current"
Else
ContractStatus = "Expired"
End If
End Select
End If

End Function

and call it in the query as:

Expr1: ContractStatus([CONTRACT END DATE])

You can of course name the column something more meaningful than the default
Expr1. Another advantage of a function is that it can be called from
anywhere within the database, so you can also call it in forms, reports or in
other functions/procedures if you wish.

By default the function will return Null if the contract end date is Null,
but if you want it to return something else by default just change the first
line of the body code accordingly.

Ken Sheridan
Stafford, England