Access Tricky IIF statement in Access - Grrrr


Joined
Jun 1, 2012
Messages
33
Reaction score
0
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 :blush:

=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])))
 
Ad

Advertisements

Joined
Jun 12, 2012
Messages
53
Reaction score
0
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.
 

Attachments

Last edited:
Ad

Advertisements

Joined
Jun 1, 2012
Messages
33
Reaction score
0
Goodfellow, you are the man!! Perfect!! :bow:, 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Iif Statements In Access 0
Access iif statement in Access report 0
Access MS Access IIF statement 0
Access MS Access IIF Help 1
Access Access IIF and Instr function 6
Access Building a IIF expression in Access 0
Access IIf function in MS Access 1
Access IIF Statement - If field is greater than other field by =/-percentage 0

Top