Calculating event dates and times in hrs and min. 2

[KenGoonan]

I hoping someone can suggest some VBA code to do the following calculations. I want to calculate in hour and minutes the total time of an event that may be less than one day, equal to one day, or more than one or two days long.

On a subform Users enter a [StartDate] in one field and [StartTime] in another field,
Users enter [EndDate] in one field and [EndTime] in another field at the end of the event.

Formats are short date and medium time. A field named [TotalTime] would receive the calculated result in hours and minutes when the [EndDate] and [EndTime] are entered. All fields are in a table named [tblTotalEventTime].

Example:

Start Date: 9/10/2009
Start Time: 09:00AM

End Date: 9/12/2009
End Time: 10:00am

Total Time: 49hrs 00min (Or 49:00)

Thanks in advance...

 

[dhookom]

You shouldn't normally store the result of the calculation and "49:00" could only be a string/text value in Access which isn't very functional.

Dates and Times are numbers so to find the duration, you can use an expression like the following to get the total minutes:
[tt][blue]=DateDiff("n",StartDate+StartTime, EndDate+EndTime)[/blue][/tt]
If you want hours, divide by 60.

Duane
Hook'D on Access
MS Access MVP

 

[KenGoonan]

Your suggestion helped me out. With a slight modification I was able to get exactly what I wanted.

A SetValue macro:
Item:
[Forms]![SfrmEventData]![TotalEventTime]

Expression:

Abs(DateDiff("n",[Forms]![SfrmEventData]![StartDate]+[Forms]![SfrmEventData]![StartTime],[Forms]![SfrmEventData]![EndDate]+[Forms]![SfrmEventData]![EndTime]))/60

Put the macro in the AfterUpdate property of the [EndTime]
Set number format to fixed, 2 decimals, and it gives hours and minutes in the [TotalEventTime].

Thanks again...

 

[KenGoonan]

The previous solution works only in the subform when is opened separately. It doesn't work when the main form is open. The error message I get tells me it can't find the subform or it is not open. Does anyone have any suggestions on how to fix this macro expression.

 

[SkipVought]

Set number format to fixed, 2 decimals, and it gives hours and minutes in the [TotalEventTime].

Not so! This is a NUMBER. A number to 2 places is 100[sup]ths[/sup] not 60[sup]ths[/sup].

If you want HOURS from Date/Time (DAYS), divide by 24 not 60.

If you want MINUTES from HOURS, divide the HOURS by 60.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Regarding the reference to the subform, your criteria must be fully qualified. Check this reference Refer to Form and Subform properties and controls

Duane
Hook'D on Access
MS Access MVP

 

[KenGoonan]

Thanks much Duane for that reference.

And Skip... you are correct. I noticed right away when I was putting in different times and dates.

I appreciate the help.