iif statement

[iuphim]

I'm trying 5 iif statements in one and it's giving me lots of error.

My statement is

iif([date received] is not null, iif([compliance]=20, iif([days]>=10, "Met",iif([compliance]=10, iif([days]>=5,"Met","Not Met"),"Not Met"),"Not Met"),"Not Met"))

Please help me correct this so it works.

 

[Vassago]

I'm trying 5 iif statements in one and it's giving me lots of error.

My statement is

iif([date received] is not null, iif([compliance]=20, iif([days]>=10, "Met",iif([compliance]=10, iif([days]>=5,"Met","Not Met"),"Not Met"),"Not Met"),"Not Met"))

Please help me correct this so it works.

I always like to do my multiple Iif statements like this the other way around so it looks more organized like this:

Iif([date received] is null, "Not Met",
Iif([Compliance] = 20 and [Days]>=10,"Met",
Iif([Compliance] = 10 and [Days]>=5,"Met","Not Met")))

Of course since you only have two options, Met or Not Met, you could just say:

Iif([date received] is not null and (([Compliance] = 20 and [Days]>=10) or ([Compliance] = 10 and [Days]>=5)),"Met","Not Met")

I think I got the formulas you wanted right. If not, play around with these ideas for a while.

 

[iuphim]

Thank you!

 

[Vassago]

Thank you!

No problem!

 

[highandwild]

To avoid these types of situations I would call a public function from the query.

Functions can be documented and laid out better.

 

[iuphim]

not sure I understand what you mean.... sorry highandwild

 

[highandwild]

No need to apologise iuphim.

This may be a bit ahead of you at the moment but have a look at it anyway. Do not worry if you do not fully understand it and ask any questions if you like.

See attached database.

Access has standard functions like IIF, DATEADD, FORMAT etc and one can develop ones own functions that one can call in a similar way and which return a value based upon the values passed to the function and logic that one has programmed in. The values passed to a function are called arguments.

There is a function, MetOrNot, that I have put together in the module.

It needs three values passed to it:

DateReceived
Compliance
Days

If you look at the code in the function you may be able to work out what is going on as it is very similar to the way in which the IIF expression that you developed was laid out.

The function is called from the fourth column of the query

MetOrNotMet: MetOrNot([DateReceived],[Compliance],[Days])

and returns a value of "Met" or "Not Met" based upon the values of
[DateReceived], [Compliance] and [Days].

 

[raskew]

Hi -

Using a function which incorporates the Switch() function (which I personally prefer) is another way around the problem.

Public Function MultiIffs(dterecd As Date, days As Integer, compliance As Integer) As String
  If IsNull(dterecd) Then
     MultiIffs = "Not Met"
  Else
     MultiIffs = Switch(compliance = 10 And days >= 5 Or compliance = 20 And days >= 10, "Met", True, "Not Met")
  End If
End Function

HTH - Bob

 

[Vassago]

Hi -

Using a function which incorporates the Switch() function (which I personally prefer) is another way around the problem.

Public Function MultiIffs(dterecd As Date, days As Integer, compliance As Integer) As String
  If IsNull(dterecd) Then
     MultiIffs = "Not Met"
  Else
     MultiIffs = Switch(compliance = 10 And days >= 5 Or compliance = 20 And days >= 10, "Met", True, "Not Met")
  End If
End Function

HTH - Bob

I like that!

 

[iuphim]

That's pretty cool. Thanks!

I have a follow up question and anyone can answer this.

I want to do another iif statement or whatever way is better to identify if if certain days fall between a certain date then it should categorize to one of the category below.

Ex. Category 0-7, 8-16, 17-23, >30

How should I write this?

Thanks!!

 

[Vassago]

That's pretty cool. Thanks!

I have a follow up question and anyone can answer this.

I want to do another iif statement or whatever way is better to identify if if certain days fall between a certain date then it should categorize to one of the category below.

Ex. Category 0-7, 8-16, 17-23, >30

How should I write this?

Thanks!!

The Dateadd function would come into play here. For example:

Dateadd("d",5,#5/6/2010#) would give me 5/11/2010.
Dateadd("d",-5,#5/6/2010#) would give me 5/1/2010.

 

[iuphim]

The Dateadd function would come into play here. For example:

Dateadd("d",5,#5/6/2010#) would give me 5/11/2010.
Dateadd("d",-5,#5/6/2010#) would give me 5/1/2010.

Thank you.

 

[iuphim]

No need to apologise iuphim.

This may be a bit ahead of you at the moment but have a look at it anyway. Do not worry if you do not fully understand it and ask any questions if you like.

See attached database.

Access has standard functions like IIF, DATEADD, FORMAT etc and one can develop ones own functions that one can call in a similar way and which return a value based upon the values passed to the function and logic that one has programmed in. The values passed to a function are called arguments.

There is a function, MetOrNot, that I have put together in the module.

It needs three values passed to it:

DateReceived
Compliance
Days

If you look at the code in the function you may be able to work out what is going on as it is very similar to the way in which the IIF expression that you developed was laid out.

The function is called from the fourth column of the query

MetOrNotMet: MetOrNot([DateReceived],[Compliance],[Days])

and returns a value of "Met" or "Not Met" based upon the values of
[DateReceived], [Compliance] and [Days].

I like this minimization a lot.

My criteria has blown out a bit to be

iff([date received] is null, null,
iif([term]=40 and [days]=1 and [eterm]>=30and [edays]=1, met,
iif([term]=40 and [days]=0 and [eterm]>=30, met,
iif([term]=20 and [days]=1 and [eterm]>=10 and [edays]=1, met,
iif([term]=20 and [days]=0 and [eterm]>=10 , met,
iif([term]=0 and [days]=0 and [eterm]<=1, met,
iif([term]=60 and [days]=1 and [eterm]>=50 and [edays]=1, met, not met))))))))

Is there a way to condense all this?

 

[highandwild]

Does this "criteria" supersede the previous one as it looks rather different?