Date expression returns 'wrong' value

[majhl]

Hello,

I'm using a DateDiff expression to calculate the difference in hours and minutes between two dates. For example,

Start date: 12/07/2017 13:20
End date: 12/07/2017 17:00

will return the correct value '4.4' (4 hours 40 mins). However, the following does not:

Start date: 12/07/2017 13:20
End date: 12/07/2017 14:00

This returns '1.4' when it should return '0.4' (40 mins). The code I've used is below:

Me.txtIntervalSampleToSampleR = DateDiff("h", Me.txtDateTimeOfSample, Me.txtDateTimeSampleReceived) & "." & (DateDiff("n", Me.txtDateTimeOfSample, Me.txtDateTimeSampleReceived) Mod 60)

What am I doing wrong?

 

[isladogs]

Access is doing what you are telling it to do.
14-13=1
0-40=-40

To me, it's confusing to use a decimal point anyway as .4 of an hour is 24 minutes

Calculate the difference between the two values then format the result as hours and minutes. Several methods of achieving that including a variation on your approach

 

[JHB]

..
Start date: 12/07/2017 13:20
End date: 12/07/2017 17:00

will return the correct value '4.4' (4 hours 40 mins). However, the following does not:

Is that correct, (I don't believe it)?
14:20 - 1 hour
15:20 - 2 hours
16:20 - 3 hours
+ 16:20 to 17:00

 

[majhl]

Is that correct, (I don't believe it)?
14:20 - 1 hour
15:20 - 2 hours
16:20 - 3 hours
+ 16:20 to 17:00

Hello,

Yes should have been '3.4'.

 

[majhl]

Hello,

I've used Brian Warnock's given below, which appears to be working well:

https://access-programmers.co.uk/forums/showthread.php?t=243445

Thanks for the replies and to Brian!

 

[arnelgp]

actually its 0.67 minutes (40 / 60) and not 0.40 minutes.

you create a function and used it in query:

Function dteDiff(startDate As Date, endDate As Date) As Double
    Dim value As Double
    Dim hr As Double
    Dim min As Double
    value = DateDiff("n", startDate, endDate)
    hr = value \ 60
    min = Round((value Mod 60) / 60, 2)
    dteDiff = hr + min
End Function

Me.txtIntervalSampleToSampleR = DteDiff( Me.txtDateTimeOfSample, Me.txtDateTimeSampleReceived)

 

[majhl]

actually its 0.67 minutes (40 / 60) and not 0.40 minutes.

Hello,

Thanks for the reply. I understand how to use the function, but not why the answer is '67' minutes rather than '40' (which seems to be common sense).

 

[arnelgp]

it that's what you like, then go.

 

[isladogs]

Every one of us got this wrong.
40 minutes is 0.67 hours

 

[Minty]

No you are all wrong !

Its 2/3rds of a hour

 

[isladogs]

No you are all wrong !

Its 2/3rds of a hour

Oh you pedant - 2/3 of an hour is 0.67 hours to 2 decimal places as you well know