DateDiff Calculation

[SYG]

Hi,

I am trying to calculate the Hours between two dates... I have one box that input date started sch_Date, one that inputs time start sch_Time, one that inputs end date sch_End date and one that inputs end time sch_EndTime.

I know i would have to use the Dateif function but is that only for two dates? How would I calculate the hours from the first date to the second using the times?

sch_Date: 5/29/12
sch_Time: 9M
sch_EndDate: 5/30/12
Sch_Endtime: 9:AM

I would like a text box on the side to come up with the calculate of 12 hours.

Any ideas?

 

[KenHigg]

You should examine the date fields to make sure you are not storing a time component as access stores time/date values in a variant data type which have a date and time component. If you only have dates in the date fld and time in the time fld the you should be able to add the two to use as elements in the datediff() function using the 'h' interval for hours...

 

[CraigDolphin]

[Edit] Ninja'd by Ken. Damned slow typing!

Datediff("h",[sch_Date]+[sch_Time],[sch_EndDate]+[sch_Endtime])

It helps to understand exactly how Access stores dates.

Access actually stores dates as a 'special' type of number. In essence, a date is the number of whole days that have elapsed since the 'midnight' between 12/31/1899 and 1/1/1900. (In access, midnight is the start of the new day, not the end of the preceding day.)

Time is also a numeric value which corresponds to the fraction of the 24 hour period which has elapsed since midnight.

A time only field stores fractional values (between 0 and 1). e.g., output from the Time() function.

A date only field contains whole numbers. e.g., output from the Date() function.

And a date/time field essentially holds a number that has some decimal places. The part of the number to the left of the decimal point corresponds to the date, and the part of the number to the right of the decimal corresponds to the time. e.g., output from the Now() function.

Thus, you can mathematically add a date-only field to a time-only field and treat the resulting value as a date/time. But we careful not to add a date/time field to another date/time field as the result will be nonsensical.

 

[SYG]

Well it says data type date/time but there is no option for just time. do i have to just set it that way in a VBA code when im doing the dateiff code? Or is there a way to choose only time or only date

 

[CraigDolphin]

Sorry...guess I wasn't as clear. There's only one type of date/time field. The difference is all in what the specific values stored in the field are.

If they are all whole numbers then it's just dates. If they are all less than one, they are only times. If they are decimal values, they are dates and times.

You just need to be sure that you're only adding times to dates, not date.time to date.time.

So if you populate your time field using the now() function, then you'd be saving both the date and the time. If you populate is with the time() function, you're only saving the time. Similarly, if you populate the date field with the date() function, you're saving only the date. But if you populate is with the Now() function then you're saving both.

 

[SYG]

ah I see... I didn't know about the ( + ) feature of the datediff. And I was putting a time in my date box. Thanks for the help it all works perfectly now

 

[KenHigg]

[Edit] Ninja'd by Ken. Damned slow typing!

Sorry -