Solved DateDiff to calculated min

[Alvein17]

Dear All,

hello i need some advice to fix my trouble.
i have a table, there is CuciMesinStart, CuciMesinEnd and Column A as a Calculated result from CuciMesinEnd - CuciMesinStart. with condition Shift 3, it's mean start from 11:00 PM until 07:00 AM.
so my question how to get a result from CuciMesinEnd-CuciMesinStart

this is my querry.

 

[theDBguy]

Hi. Not sure I understand your question. Can you maybe post some mockup using Excel to show the current data and the result you from them? Thanks.

 

[arnelgp]

A: IIf(TimeValue([CuciMesinEnd])<TimeValue([CuciMesinStart]),
DateDiff("n",[CuciMesinStart],DateAdd("d",1,[CuciMesinEnd])),
DateDiff("n",[CuciMesinStart],[CuciMesinEnd]))\60 & ":" &
(IIf(TimeValue([CuciMesinEnd])<TimeValue([CuciMesinStart]),
DateDiff("n",[CuciMesinStart],DateAdd("d",1,[CuciMesinEnd])),
DateDiff("n",[CuciMesinStart],[CuciMesinEnd])) Mod 60)

 

[Alvein17]

Hi. Not sure I understand your question. Can you maybe post some mockup using Excel to show the current data and the result you from them? Thanks.

hello, this i attached mockup by excel file. actually i have do it by excel, but when i move to Access querry i got some toruble with this.

 

[arnelgp]

A: IIf(IsNull([CuciMesinStart]+[CuciMesinEnd]),
0,
DateDiff("n",CDate(Date() & " " & [CuciMesinStart]),
CDate(Date()-([cuciMesinStart]>[CuciMesinEnd]) & " " & [CuciMesinEnd])))

 

[Sun_Force]

A: IIf(IsNull([CuciMesinStart]+[CuciMesinEnd]),
0,
DateDiff("n",CDate(Date() & " " & [CuciMesinStart]),
CDate(Date()-([cuciMesinStart]>[CuciMesinEnd]) & " " & [CuciMesinEnd])))

@arnelgp you answered my question here :https://www.access-programmers.co.u...-between-two-given-times.319408/#post-1783588
I didn't know I can use someDate & " " & someTime as a parameter for DateDiff.
You've always been a savior.
Thanks.

 

[Alvein17]

A: IIf(TimeValue([CuciMesinEnd])<TimeValue([CuciMesinStart]),
DateDiff("n",[CuciMesinStart],DateAdd("d",1,[CuciMesinEnd])),
DateDiff("n",[CuciMesinStart],[CuciMesinEnd]))\60 & ":" &
(IIf(TimeValue([CuciMesinEnd])<TimeValue([CuciMesinStart]),
DateDiff("n",[CuciMesinStart],DateAdd("d",1,[CuciMesinEnd])),
DateDiff("n",[CuciMesinStart],[CuciMesinEnd])) Mod 60)

Hello Arnelg, thank you for your help. it's work so well.