Access Tricky IIF statement in Access - Grrrr

[Bar_NZ]

Hey all, I have a very tricky IIF statement Im using in Access 2010...

What I have - a PlaceKPI time [PlaceKPI12] and a Place Actual time [PlaceAct12] the KPI time is 23:00 hrs {11pm} and the place Actual time can be anywhere from 22:00 hrs {10pm} through to 03:00 hrs {3am}.

If the time is after 23:00, it should show a - result in Minutes (Display's correctly)
If the time is after midnight shows a negitive result (Display's correctly)
However if the time ACT place time is early, it shows a negitive result (Not right)

Below is the statement I'm useing, can anyone read this and see my mistake, I would really appreciate your help.

Cheers
Bar_NZ

=IIf([PlaceKPI12]>[PlaceACT12] And Abs(DateDiff("n",[PlaceKPI12],[PlaceACT12]))>120,DateDiff("n",DateAdd("n",1440,[PlaceACT12]),[PlaceKPI12]),IIf([PlaceKPI12]<[PlaceACT12] And Abs(DateDiff("n",[PlaceKPI12],[PlaceACT12]))>120,DateDiff("n",DateAdd("n",1440,[PlaceKPI12]),[PlaceACT12]),DateDiff("n",[PlaceKPI12],[PlaceACT12])))

 

[goodfellow]

OK, let's try again . Here's a bit less trickier solution (or maybe "workaround" would be better word):

=IIf(Abs(DateDiff("n", DateAdd("n", 200, [PlaceKPI12]), DateAdd("n", 200, [PlaceACT12]))) > 120, DateDiff("n", DateAdd("n", 200, [PlaceKPI12]), DateAdd("n", 200, [PlaceACT12])) * -1 - 1440, DateDiff("n", DateAdd("n", 200, [PlaceKPI12]), DateAdd("n", 200, [PlaceACT12])) * -1)

Sample results of this query are in attachment.
Trick is to move both dates to the next day by adding 200 minutes to them and then count the difference. If the span is more than 120 you should decrease it by 24h (1440 min). And "* -1" is to adapt the sign to your needs.

If something is still wrong, let me know.

 

[Bar_NZ]

Goodfellow, you are the man!! Perfect!! , Thank you my friend, I really appreciate your persistance with me.

I search and search, then come to the forum, my main issue is, not so much the basics and reading the code, but when it gets relitive complex like this one, I look and look, trying to see what it's doing, but not quite getting there.

I'm sure I will have more questions later during the building of this DB, I aslo have been tasked to build another one, thats about 50 times more complex, however using the above IIF will help greatly.

Thanks Again