DateDiff Problem

[JayAndy]

I am trying to work out a difference between two time from a form.

I have a form the people put the Start Time and End Time Then in my VBA l have the following code.

Dim Lvalue As Integer

StartTime = [Forms]![Time]![Start]
EndTime = [Forms]![Time]![End]

Lvalue = DateDiff("n", StartTime, EndTime) / 60

but the Result comes out at 8 hrs if people put 9AM to 3:30PM round up to the nearest hour

Anyone got any suggestion why this is happening.

 

[CJ_London]

first off, Time and End are reserved words, using them as object/field/table names can cause unexpected issues. See this link for reserved words

https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe

to clarify your question - you are saying someone enters 9am and 3:30pm and gets 8 hours when the expected value is 6.5?

either way, you have declared lvalue as an integer - date/time is a form of double - so lvalue should be dimmed as double as well

 

[JayAndy]

The Changing to double worked thank you

 

[The_Doc_Man]

The best way to understand the way Access handles dates/times is to realize that it is a "synthetic" variable as opposed to a native data type. Date/time data types are actually casts (a.k.a. typecasts) of DOUBLE data types.

The difference IN DAYS AND FRACTIONS between two times is dblDiff = CDbl( Time2 ) - CDbl( Time1 ). To get this into hours, multiply by 24. To get it into minutes (from days) multiply by 1440 (=60*24). Note that the DOUBLE variable cannot be properly handled by the time formatting routines if you convert it to some other unit. An elapsed time computed as shown above CAN be converted with Format$(dblDiff, "hhh:nn:ss") - and the triple-H is crucial in this case.

 

[CJ_London]

triple h is a new one for me!

 

[Cronk]

[FONT=&quot]Eh?[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Immediate window:[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]? format(cdbl(#9:00PM#) - cdbl(#3:15PM#) ,"hhh:nn:ss")
055:45:00[/FONT][FONT=&quot][/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]? format(cdbl(#9:00PM#) - cdbl(#3:15PM#) ,"hh:nn:ss")
05:45:00[/FONT][FONT=&quot][/FONT]