If statement calculated fields help

[dandaman098]

Hi
Access 2013
I've created a table for a college project and realised that one of my calculated fields isn't working. It doesn't produce an error message, it just doesn't calculate. Any help will be appreciated.

IIf([CycleSpeed]<10,[CycleTime]*236,IIf(10<=[CycleSpeed]<12,[CycleTime]*354,IIf(12<=[CycleSpeed]<14,[CycleTime]*472,IIf(14<=[CycleSpeed]<16,[CycleTime]*590,IIf(16<=[CycleSpeed]<=20,[CycleTime]*708,IIf([CycleSpeed]>20,[CycleTime]*944,0))))))

 

[Trevor G]

I don't use Calculations in a table but looking at the IIF you refer to a Field name in your first IIF then don't at the beginning of the rest of the IIF statements in your nest. Extracted part of your IIF is below:

IIf([CycleSpeed]<10,[CycleTime]*236,IIf(10<=[CycleSpeed]<12

 

[dandaman098]

I have tried rearranging the equation to have [cycletime] at the beginning of the iff statement in a variety of ways but keep getting syntax errors. What would be the best way to find out if [cycletime] is between two numbers?

 

[CJ_London]

10<=[CycleSpeed]<12

this won't work - you need

10<=[CycleSpeed] AND [CycleSpeed]<12

but your whole formula is more complex than it needs to be - the first part of the iif has already determined if [CycleSpeed]<10

try using the switch function instead

Switch([CycleSpeed]<10,[CycleTime]*236,[CycleSpeed]<12,[CycleTime]*354,[CycleSpeed]<14,[CycleTime]*472,[CycleSpeed]<16,[CycleTime]*590,[CycleSpeed]<=20,[CycleTime]*708,[CycleSpeed]>20,[CycleTime]*944)

or even clearer
[CycleTime]*Switch([CycleSpeed]<10,236,[CycleSpeed]<12,354,[CycleSpeed]<14,472,[CycleSpeed]<16,590,[CycleSpeed]<=20,708,[CycleSpeed]>20,944)

 

[Trevor G]

If you consider a query then the criteria would be >Number and <number. You can then build the IIF against this and do it one at a time and then look to combine them.

 

[pbaldy]

I don't think you can have a compound test like that, it would need to be

[CycleSpeed] >= 10 And [CycleSpeed]<12

but you can take advantage of the fact that Access will return the first true value, and simply use < in order, like:

IIf([CycleSpeed] < 10, blah, IIf([CycleSpeed] < 12, blah, ...

You might also look at the Switch() function, or storing the ranges and values in a table and dropping the calculated field, getting the values via a join in queries.

 

[pbaldy]

This is what happens when you get a phone call while typing.