This query works fine when there is a value in the [Route] field.
It doesn't if it is null . I can fix this but it would require another embedded IIf statement that is a duplicate of the 2nd one. Is there an easier way?
The first portion should apply only if the 1st character of the [Route] field is 7. The second portion should apply for anything else including a null value in the [Route] field.
LockPts: IIf(Left$([Assignments]![Route],1)=7,IIf([Employee Stats]![Sum Of Locks]=0 Or [Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<3.6,30,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<4.2,20,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<4.8,15,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<5.4,10,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<6,5,0))))),
IIf([Employee Stats]![Sum Of Locks]=0 Or [Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<4.8,30,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<5.6,20,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<6.4,15,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<7.2,10,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<8,5,0))))))
It doesn't if it is null . I can fix this but it would require another embedded IIf statement that is a duplicate of the 2nd one. Is there an easier way?
The first portion should apply only if the 1st character of the [Route] field is 7. The second portion should apply for anything else including a null value in the [Route] field.
LockPts: IIf(Left$([Assignments]![Route],1)=7,IIf([Employee Stats]![Sum Of Locks]=0 Or [Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<3.6,30,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<4.2,20,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<4.8,15,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<5.4,10,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<6,5,0))))),
IIf([Employee Stats]![Sum Of Locks]=0 Or [Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<4.8,30,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<5.6,20,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<6.4,15,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<7.2,10,IIf([Employee Stats]![Sum Of Locks]/[Employee Stats]![Sum Of Reads]*1000<8,5,0))))))
