In Microsoft Access, I have two text boxes; t_on and t_off. Both contain a ‘short time’ in the format ‘HH:MM:SS’.
I also have another text box; Total_Hours
My question is, how do I get the Total_Hours text box to display how many hours have elapsed between the t_on and t_off values, to the nearest 15 mins? This needs to be returned in decimal format, eg. 7.25, 7.5, 7.75, 8.
Can anyone help?? It should save people making mistakes! Thanks 🙂
Update:
Antonio:
Thanks, but can anyone help me with how I integrate this into VB? I have only used VB in Excel, and this seems a little different..!
Update 2:
iLC – Thanks, I have the module created.
What I don’t understand is how to call this sub on the After_Update of the t_on or t_off text boxes for each record entry?
Cheers!
2
✅ Answers
? Favorite Answer
Hope this can help :
Dim minutes=DateDiff(“n”, t_on, t_off) ;//DIFF IN MINUTES
Dim hours=minutes60 ;//HOURS
Dim fraction=(((minutes MOD hours)15)+1)/4;//FRACTIONS 0,25 0,5 0,75 1
Dim Total_Hours=hours+fraction; //for example 7+0.25
I dont remember whell Microsoft Access but the code above is in Visual Basic so I think it can be usefull
Source(s): http://msdn.microsoft.com/it-it/library/b5xbyt6f%2…
create an MS Access module and paste the following code snippets:
Option Compare Database
Option Explicit
Sub Diff(t_on, t_off)
Dim minutes: minutes = DateDiff(“n”, t_on, t_off) ‘DIFF IN MINUTES
Dim hours: hours = minutes 60 ‘HOURS
Dim fraction: fraction = Int((minutes Mod 60) / 15 + 0.5) / 4 ‘FRACTIONS to NEAREST 0.25
Dim Total_Hours: Total_Hours = hours + fraction ‘for example 7+0.25
Debug.Print Total_Hours
End Sub
In your immediate window you can test the function as:
Call Diff(#12:30#,#14:40#)
Ans: 2.25
Call Diff(#12:30#,#14:#)
Ans: 1.5
Call Diff(#12:30#,#13:50#)
Ans: 1.25
Regards
iLC