Access Access 2010 Time before and after midnight!!!

[Bar_NZ]

Please help, I have been trying to calculate tims in a form for show (Datediff) in minutes ("n") for items that happen before midnight and sometimes after midnight.

What I have so far:

For day time's i use DateDiff("n",[KPI_Time],[ACT_Time])
ACT_Time is acutal time item was placed. Works great for anything that does not go over midnight.

For over midnight I use =IIf([Text0]<[Text2],DateDiff("n",[Text0],[Text2]),1440-DateDiff("n",[Text2],[Text0]))

So Place time KPI is 23:00 hours, The ACT time can be anywhere from 22:00 - 01:30

I just need to display minutes as -10 is 10 minutes early and 10 is ten minutes late, up to 120 minutes early or late...

Please help,

 

[goodfellow]

Could you explain exactly where is the problem? Your solution:

=If([Text0]<[Text2],DateDiff("n",[Text0],[Text2]),1440-DateDiff("n",[Text2],[Text0]))

seems to work perfectly fine, for calculating minutes over midnight...

 

[Bar_NZ]

Hey Goodfellow, thanks for this, you are correct it does work after midnight, my main problem is my calculation can be before or after midnight.
E.g. KPI time is 11pm, the time for the calculation could be anywhere from 10 pm through to 2 am. So this does work in minutes after but not before, I tried a nested iif, but dosnt seem to work.

Hope this explains it (ish)

Cheers

B

 

[goodfellow]

Try this. It's based on what you wrote, that time difference is allways less than 120 minutes. I simply consider times with bigger difference as not valid and adding one day to one of the times (dates)... If it is not what you want exactly, maybe it will point you the right direction at least

IIf([Text0]>[Text2] And Abs(DateDiff("n",[Text0],[Text2]))>120,DateDiff("n",DateAdd("n",1440,[Text2]),[Text0]),IIf([Text0]<[Text2] And Abs(DateDiff("n",[Text0],[Text2]))>120,DateDiff("n",DateAdd("n",1440,[Text0]),[Text2]),DateDiff("n",[Text0],[Text2])))

Cheers

 

[Bar_NZ]

Thanks Goodfellow, this is good, however my answer now is 2056390, any ideas.. Sorry, im not very good

 

[goodfellow]

Are you sure you copied entire query? It's quite long, much longer than text field.

I have attached example table with dates and calculations performed by this query. There are all possibilities there:

• Text0 > Text2 and Text2 is past the midnight
• Text0 < Text2 and Text0 is past the midnight
• Text0 < Text2 and both are before midnight
• Text0 > Text2 and both are before midnight

Text0 and Text2 fields including dates so you can see that calculations are performed properly even though real time span is different.

 

[Bar_NZ]

Ok, I'm trying to do this within a text box in a form first, then I will write the query after the fact, I think I will try the query first, then see if I can translate these into the form... I have attached a screen dump of the testing form I trying this on... Although my naming conventions are a bit misleading... PlaceTest_After is where I enter the time to calculate form the KPITest Field.. The other two fields are to show the results before and after Midnight...

I really appreciate your help with this, I've been pulling my hair out, and I can't afford that I don't have much left, lol...

 

[Bar_NZ]

PERFECT!!! yay, it's working awesome, thank you so much for all of your help... may I call again??

 

[goodfellow]

Any time
I'm glad I could help.
Cheers.

 

[Bar_NZ]

Hello again Goodfellow, it's me, the pain in the butt again.. anyway I have another question if I may?

I have (In a form) a series of times that are usually static.. However now I have been asked to have the ability to update a series of time if the first part is running late.

Screen Shot attached...

If you see the Pull KPI and Place KPI these are static times, if the first row is say late by two hours, then I need to adjust all other times by 2 hours, if the third line is late the all the remaining times need to be updated, but not the ones before.. They have asked me to add a button and text box (With the time to update in hours and minutes), that will automatically add the time changes to the remaing KPI times (Pull and Place).. So I'm assuming that I would need the ability for a pop up to ask how much time from what movement, type of thing.

Nothing like asking the easy questions eah?

Cheers

B

 

[Bar_NZ]

Does help if I actually attach the file.. Lol

 

[goodfellow]

Hi Bar_NZ,
If your only problem is how to get user info from which row to start recalculating times and how much time to add, then you have no problem
I think that pop-up may be not necessary. I would place on the form three new items:
- combo box (M1, M2...M13), to choose from which row to start recalculation
- text box, to input amount of time to add
- button ("Recalculate Times"), to finish your job
Hope this would help, cheers

 

[Bar_NZ]

Yes it does, wht I dont know is the code that goes behind the button