IIF Criteria levels

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How many levels can you have to an IIf statment? I know Excel you can only go
seven of eight levels down. I have the following statment and I need to add
one more for 1000 - 1400. When I add it I receive a too complex message. Any
suggestions on simplifying this would be much appreciated as well. Thanks in
advance.

Late: IIf(([TIMEFRAME]="0800-1000") And ([START
TIME])>"10:00",1,IIf(([TIMEFRAME]="0830-1200") And ([START
TIME])>"12:00",1,IIf(([TIMEFRAME]="1000-1200") And ([START
TIME])>"12:00",1,IIf(([TIMEFRAME]="1200-1400") And ([START
TIME])>"14:00",1,IIf(([TIMEFRAME]="1200-1600") And ([START
TIME])>"16:00",1,IIf(([TIMEFRAME]="1400-1600") And ([START
TIME])>"16:00",1,IIf(([TIMEFRAME]="1400-1800") And ([START
TIME])>"18:00",1,IIf(([TIMEFRAME]="1600-1800") And ([START
TIME])>"18:00",1,IIf(([TIMEFRAME]="1600-1900") And ([START
TIME])>"19:00",1,IIf(([TIMEFRAME]="1600-2000") And ([START
TIME])>"20:00",1,IIf(([TIMEFRAME]="1700-1900") And ([START
TIME])>"19:00",1,IIf(([TIMEFRAME]="1800-2000") And ([START
TIME])>"20:00",1,IIf(([TIMEFRAME]="0800-2000") And ([START
TIME])>"20:00",1,0)))))))))))))
 
You could use the Switch function instead or you could use one long
condition.

Late: IIf(([TIMEFRAME]="0800-1000" And [START TIME]>"10:00")
OR ([TIMEFRAME]="0830-1200" And [START TIME]>"12:00")
OR ([TIMEFRAME]="1000-1200" And [START TIME]>"12:00")
OR ([TIMEFRAME]="1200-1400" And [START TIME]>"14:00")
OR ([TIMEFRAME]="1200-1600" And ([START TIME]>"16:00")
....
OR (TIMEFRAME]="0800-2000" And [START TIME]>"20:00"),1,0)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
How about:

Since your [TimeFrame] fields all appear to be formatted the same (9
characters), and your [StartTime] field appears to be formatted similiarly,
you might be able to do something like:

Late: IIF(mid([TimeFrame],6) < Replace([StartTime], ":", ""), 1, 0)

HTH
Dale
 
Worked Perfectly, to sweet. I wouldn't have thought about this approach plus
I see where it allows for flexability incase the timeframes would change.

Dale Fye said:
How about:

Since your [TimeFrame] fields all appear to be formatted the same (9
characters), and your [StartTime] field appears to be formatted similiarly,
you might be able to do something like:

Late: IIF(mid([TimeFrame],6) < Replace([StartTime], ":", ""), 1, 0)

HTH
Dale


Dan said:
How many levels can you have to an IIf statment? I know Excel you can only
go
seven of eight levels down. I have the following statment and I need to
add
one more for 1000 - 1400. When I add it I receive a too complex message.
Any
suggestions on simplifying this would be much appreciated as well. Thanks
in
advance.

Late: IIf(([TIMEFRAME]="0800-1000") And ([START
TIME])>"10:00",1,IIf(([TIMEFRAME]="0830-1200") And ([START
TIME])>"12:00",1,IIf(([TIMEFRAME]="1000-1200") And ([START
TIME])>"12:00",1,IIf(([TIMEFRAME]="1200-1400") And ([START
TIME])>"14:00",1,IIf(([TIMEFRAME]="1200-1600") And ([START
TIME])>"16:00",1,IIf(([TIMEFRAME]="1400-1600") And ([START
TIME])>"16:00",1,IIf(([TIMEFRAME]="1400-1800") And ([START
TIME])>"18:00",1,IIf(([TIMEFRAME]="1600-1800") And ([START
TIME])>"18:00",1,IIf(([TIMEFRAME]="1600-1900") And ([START
TIME])>"19:00",1,IIf(([TIMEFRAME]="1600-2000") And ([START
TIME])>"20:00",1,IIf(([TIMEFRAME]="1700-1900") And ([START
TIME])>"19:00",1,IIf(([TIMEFRAME]="1800-2000") And ([START
TIME])>"20:00",1,IIf(([TIMEFRAME]="0800-2000") And ([START
TIME])>"20:00",1,0)))))))))))))
Click to expand...
Click to expand...
 
Worked Fabulously, I was afraid I was running to deep or reaching a criteria
limit. also a littel easier to follow.Thanks

John Spencer said:
You could use the Switch function instead or you could use one long
condition.

Late: IIf(([TIMEFRAME]="0800-1000" And [START TIME]>"10:00")
OR ([TIMEFRAME]="0830-1200" And [START TIME]>"12:00")
OR ([TIMEFRAME]="1000-1200" And [START TIME]>"12:00")
OR ([TIMEFRAME]="1200-1400" And [START TIME]>"14:00")
OR ([TIMEFRAME]="1200-1600" And ([START TIME]>"16:00")
....
OR (TIMEFRAME]="0800-2000" And [START TIME]>"20:00"),1,0)


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

Dan said:
How many levels can you have to an IIf statment? I know Excel you can only
go
seven of eight levels down. I have the following statment and I need to
add
one more for 1000 - 1400. When I add it I receive a too complex message.
Any
suggestions on simplifying this would be much appreciated as well. Thanks
in
advance.

Late: IIf(([TIMEFRAME]="0800-1000") And ([START
TIME])>"10:00",1,IIf(([TIMEFRAME]="0830-1200") And ([START
TIME])>"12:00",1,IIf(([TIMEFRAME]="1000-1200") And ([START
TIME])>"12:00",1,IIf(([TIMEFRAME]="1200-1400") And ([START
TIME])>"14:00",1,IIf(([TIMEFRAME]="1200-1600") And ([START
TIME])>"16:00",1,IIf(([TIMEFRAME]="1400-1600") And ([START
TIME])>"16:00",1,IIf(([TIMEFRAME]="1400-1800") And ([START
TIME])>"18:00",1,IIf(([TIMEFRAME]="1600-1800") And ([START
TIME])>"18:00",1,IIf(([TIMEFRAME]="1600-1900") And ([START
TIME])>"19:00",1,IIf(([TIMEFRAME]="1600-2000") And ([START
TIME])>"20:00",1,IIf(([TIMEFRAME]="1700-1900") And ([START
TIME])>"19:00",1,IIf(([TIMEFRAME]="1800-2000") And ([START
TIME])>"20:00",1,IIf(([TIMEFRAME]="0800-2000") And ([START
TIME])>"20:00",1,0)))))))))))))
Click to expand...
Click to expand...
 
The other advantage of this should be increased speed. With IIF statements,
Access processes all of the conditional possibilities, even after it finds
the first one that is true.

At least that is what I have heard. As I recall, I found a way to confirm
that once, but don't remember what it was. Oh yeah try this in the immediate
window:

?IIF(1<2, True, iif(2 = "a", "b", "c")) it will give you a type mismatch in
the 2nd comparison. even though the first criteria is true.

Dale
--
Email address is not valid.
Please reply to newsgroup only.


Dan said:
Worked Perfectly, to sweet. I wouldn't have thought about this approach plus
I see where it allows for flexability incase the timeframes would change.

Dale Fye said:
How about:

Since your [TimeFrame] fields all appear to be formatted the same (9
characters), and your [StartTime] field appears to be formatted similiarly,
you might be able to do something like:

Late: IIF(mid([TimeFrame],6) < Replace([StartTime], ":", ""), 1, 0)

HTH
Dale


Dan said:
How many levels can you have to an IIf statment? I know Excel you can only
go
seven of eight levels down. I have the following statment and I need to
add
one more for 1000 - 1400. When I add it I receive a too complex message.
Any
suggestions on simplifying this would be much appreciated as well. Thanks
in
advance.

Late: IIf(([TIMEFRAME]="0800-1000") And ([START
TIME])>"10:00",1,IIf(([TIMEFRAME]="0830-1200") And ([START
TIME])>"12:00",1,IIf(([TIMEFRAME]="1000-1200") And ([START
TIME])>"12:00",1,IIf(([TIMEFRAME]="1200-1400") And ([START
TIME])>"14:00",1,IIf(([TIMEFRAME]="1200-1600") And ([START
TIME])>"16:00",1,IIf(([TIMEFRAME]="1400-1600") And ([START
TIME])>"16:00",1,IIf(([TIMEFRAME]="1400-1800") And ([START
TIME])>"18:00",1,IIf(([TIMEFRAME]="1600-1800") And ([START
TIME])>"18:00",1,IIf(([TIMEFRAME]="1600-1900") And ([START
TIME])>"19:00",1,IIf(([TIMEFRAME]="1600-2000") And ([START
TIME])>"20:00",1,IIf(([TIMEFRAME]="1700-1900") And ([START
TIME])>"19:00",1,IIf(([TIMEFRAME]="1800-2000") And ([START
TIME])>"20:00",1,IIf(([TIMEFRAME]="0800-2000") And ([START
TIME])>"20:00",1,0)))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
Dale said:
The other advantage of this should be increased speed. With IIF statements,
Access processes all of the conditional possibilities, even after it finds
the first one that is true.

At least that is what I have heard. As I recall, I found a way to confirm
that once, but don't remember what it was. Oh yeah try this in the immediate
window:

?IIF(1<2, True, iif(2 = "a", "b", "c")) it will give you a type mismatch in
the 2nd comparison. even though the first criteria is true.
Click to expand...


Dale,

You are correct in the VBA environment, but the Expression
Service environment (SQL and ControlSource expressions), the
IIf function is different. One difference is that the third
argument defaults to Null when it is omitted. The big
difference is that it does not evaluate the false part if
the true part is used. To test this try using:
IIf(True, MsgBox("True part"), MsgBox("False part"))
in those three different situations.
 
Back
Top