nested IIF calculation

[FameAsser]

Hi guys.

I am driving myself insane trying to write this and could really do with help.

Basic outline is that players predict the attendance of a football game. We award 1 point if they guess within 500 of the attendance, 2 points if within 250, 3 points if within 100, 4 points if within 50 and 10 points if they guess the exact attendance.

So, I have "t_matches.attendance" which is where the prediction has been posted. Then we have "t_fixtures.attendance" where the actual attendance has been posted.

I started off writing this (the null test is to make sure that if there's nothing in t_matches, they automatically receive zero points :

Expr1: IIf(IsNull([t_matches].[attendance]),0,IIf([t_matches].[attendance]>([t_fixtures].[attendance]+500),0,IIf([t_matches].[attendance]<([t_fixtures].[attendance]-500),0,IIf([t_matches].[attendance]>([t_fixtures].[attendance]+250),1,IIf([t_matches].[attendance]>([t_fixtures].[attendance]-250),1,IIf([t_matches].[attendance]>([t_fixtures].[attendance]+100),2,IIf([t_matches].[attendance]<([t_fixtures].[attendance]-100),2,5)))))))

But so far, all results are coming back with a 1 even when the prediction is within 250 or 100 etc (the 5 at the end was just a placeholder.)

Would someone be able to point me in the right direction to be able to get the full range of points returning?

Regards

 

[Brianwarnock]

When comparing against the - situation shouldn't it be <

Brian

 

[FameAsser]

When comparing against the - situation shouldn't it be <

Brian

Wow! Thank you for spotting that!

It is working now............haha...apologies for time wasting

 

[Brianwarnock]

No need to apologise, our own errors , typos , syntax , and the simple logic ones, are the hardest for us to spot, and we have all been there.

Brian