IIf Function

  • Thread starter Thread starter CharlesCount via AccessMonster.com
  • Start date Start date
C

CharlesCount via AccessMonster.com

Can I use an IIF expression for the following and how would it look?
I have several (15) destinations identified by numbers and a time for each
location. I need to calculate the departure time from a fixed location by
using different known travel times to all locations.

If destination is 15 then arrival time less 25 to get starting time, if
destination is 16 then arrival time less 10 to get starting time, If
destination is 17 then less 18 to get starting time Ect.

Thanks

Charles
 
IIf is one way to do it:

StartingTime: ArrivalTime - IIf(Destination=15, 25, IIf(Destination=16, 10,
IIf(Destination=17, 18, 0)))


Another way of doing it is to use the Choose function, where the argument is
selected based on value of Destination (starting with 1):

StartingTime: ArrivalTime -
Choose(Destination,0,0,0,0,0,0,0,0,0,0,0,0,0,0,25,10,18,0)


If Destination doesn't start at 1, and instead starts at, say, 15, then this
would work:

StartingTime: ArrivalTime - Choose(Destination - 14,25,10,18,0)


Check VBA Help file for more information about IIf and Choose functions.
 
It sounds as though you may be better off having a table with all of your
destinations and journey times (2 fields). You could then link to this table
for your calculation.

Alternatively you could nest your iif statements:
iif(x=15,y-25,iif(x=16,y-10,iif(x=17,y-18)))
but this is slow. There might also be a limit to the number of IIfs (13?)

Basil
 
I like the idea of the table, much simplers than the IIf. It will also be
easer to change depending on future conditions.

Thanks

Charles
It sounds as though you may be better off having a table with all of your
destinations and journey times (2 fields). You could then link to this table
for your calculation.

Alternatively you could nest your iif statements:
iif(x=15,y-25,iif(x=16,y-10,iif(x=17,y-18)))
but this is slow. There might also be a limit to the number of IIfs (13?)

Basil
Can I use an IIF expression for the following and how would it look?
I have several (15) destinations identified by numbers and a time for each
Click to expand...
[quoted text clipped - 8 lines]
Charles
Click to expand...
Click to expand...
 
Back
Top