Calculating time difference in minutes (1 Viewer)

[jpl458]

I have 3 text boxes;

starttime and endTime for formatted as Long Time
Duration is formatted as Shorttime, just interested in minutes and seconds.

Just read Allen Browne's article Calculating Elapsed Time which reads:
Let's assume a date/time field named StartDateTime to record when the employee clocks on, and another named EndDateTime for when the employee clocks off. To calculate the time worked, create a query into this table, and type this into the Field row of the query design grid:
Minutes: DateDiff("n", [StartDateTime], [EndDateTime])
Minutes is the alias for the calculated field; you could use any name you like. You must use "n" for DateDiff() to return minutes: "m" returns months.

To display this value as hours and minutes on your report, use a text box with this Control Source:

=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

I don't understand what he means buy "create a query into this table", what table? I don't think what I want is rocket science, but I can't figure it out.

Any help would be greatly appreciated

 

[MajP]

what table?

You have a table with the fields starttime and endtime. Your form is likely bound to that table. Instead of binding to a table you can bind it to a query. In that query create your calculated columns for elapsed time.
The other technique is to make a calculated control instead of doing the calculations in a query. The calculated textbox would refence the starttime and endtime fields.

 

[jpl458]

Your form is bound to a table (or maybe a query) because it has the fields starttime and endtime bound to the two controls. Instead of binding to a table you can bind it to a query that includes the table and your calculated field. Or instead of doing the calculation in the query you can make your duration textbox a calculated control.

I sorry, but all I want to do is subtract one in a time (In a textbox) from another time (also in a textbox) and put the answer in a third textbox using Datediff. I am trying to learn this, so please, how do I code the subtraction, plain and simple.

 

[CJ_London]

In the third text box controlsource put

=datediff(“n”, firsttextboxname,secondtextboxname)

if you want an exact answer, provide details like text box names, how they are formatted and some example data as the above may need tweaking.

be aware that date and time is stored as a double type number, so if you are entering text, it will need converting to a number

 

[MajP]

Query2 Query2

startTime	endTime	ElapsedMinutes	DurationHourMinutes
12:00​	12:25​	25​	0:25
12:00​	13:15​	75​	1:15
12:00​	15:22​	202​	3:22

Bind that query to the form instead of the table.

Doing this at one time in a calculated control is tedious

=DateDiff("n",[startTime],[EndTime])\60 & Format(DateDiff("n",[startTime],[EndTime]) Mod 60,"\:00")

I personally rarely do calculated controls on the form, and instead do calculated columns in the query. I find it just easier to debug in the query environment. Also you can do what I showed and do intermediate calculations. I calculated the ElapsedMinutes first then used it in the formatted DurationHoursMinutes column. This way do not have to do it all in one bite. I do not have to show ElapsedMinutes on the form.

 

[Gasman]

Perhaps it should be 'create a query from a table'

 

[jpl458]

I sorry, but all I want to do is subtract one in a time (In a textbox) from another time (also in a textbox) and put the answer in a third textbox using Datediff. I am trying to learn this, so please, how do I code the subtraction, plain and simple.

View attachment 102927
Query2 Query2

startTime	endTime	ElapsedMinutes	DurationHourMinutes
12:00​	12:25​	25​	0:25
12:00​	13:15​	75​	1:15
12:00​	15:22​	202​	3:22

Bind that query to the form instead of the table.

Doing this at one time in a calculated control is tedious

=DateDiff("n",[startTime],[EndTime])\60 & Format(DateDiff("n",[startTime],[EndTime]) Mod 60,"\:00")

Thanks for the quick, detailed response. The two controls that have start and time in them, get their data from.

Me.starttime = Now()

I want the user to click the start box at the start , the click the endtime box when it's over, and when user tabs out of end time then duration is calculated.

The Mastertbl is currently bound to the form, so when the last of many boxes is exited the data is written to the table, basic data entry. So it seems that if I bind the form to this query, what happens to the other 10 or so textboxes that are now bound to the table..

Boom, a light just went on. I ran the query and it calculated duration, but the format was all wrong.

I just realized that I don't need to have the duration Textbox at all, but have to figure out when to run the query. I would like to happen as the last box is tabbed out of. Could I use a DoCmd runsql and run this query then?

Thanks for the help and getting me thinking in the right direction.

I'll be back!4

 

[The_Doc_Man]

Boom, a light just went on. I ran the query and it calculated duration, but the format was all wrong.

I just realized that I don't need to have the duration Textbox at all, but have to figure out when to run the query.

Yes, and let me be presumptuous enough to congratulate you for a valuable revelation. Queries are your friends and in fact are the true workhorses of any SQL-based system (e.g. Access). Although there are ALWAYS exceptions, you usually can base forms and reports - and even other queries - from queries. Also, if you were writing VBA code to play with recordsets, THAT can be based on a query as well. I'll say it again: Queries are your friends once you get to know them.

 

[MajP]

Your second image is not what I did. Relook at the DurationHoursMinutes calculated field.

Mine

Thus the error in your third image.

The Mastertbl is currently bound to the form, so when the last of many boxes is exited the data is written to the table, basic data entry. So it seems that if I bind the form to this query, what happens to the other 10 or so textboxes that are now bound to the tabl

That is not how a query works. That is a dynamic column so nothing is stored or has to be forced to calculate. This is similar to a column in Excel that has a formula in it. If column C sums up columns A and B, you just enter values in A and B and column C gets dynamically updated.

 

[jpl458]

Your second image is not what I did. Relook at the DurationHoursMinutes calculated field.

View attachment 102937

Mine

View attachment 102938
Thus the error in your third image.


That is not how a query works. That is a dynamic column so nothing is stored or has to be forced to calculate. This is similar to a column in Excel that has a formula in it. If column C sums up columns A and B, you just enter values in A and B and column C gets dynamically updated.

I'm going back to basics. I have a start time and an end time and I want the difference between the 2, the basics of which would look something like this:

Me.Durationtb = Me.starttime - Me.endtime

I think this would work if I got the data types correct and used format, and had the right punctuation, and that's the part I am weakest with. Just so you know, I wrote a mile of code in 360/370 assembler language, as well as Fortran, PL1, COBAL. and others, but that was then. So can you help me, or point me to the proper tutorial.

Thanks for your help

 

[GPGeorge]

I'm going back to basics. I have a start time and an end time and I want the difference between the 2, the basics of which would look something like this:

Me.Durationtb = Me.starttime - Me.endtime

I think this would work if I got the data types correct and used format, and had the right punctuation, and that's the part I am weakest with. Just so you know, I wrote a mile of code in 360/370 assembler language, as well as Fortran, PL1, COBAL. and others, but that was then. So can you help me, or point me to the proper tutorial.

Thanks for your help

PMFJI:

Look again at MajP's post #9. He illustrated the most appropriate way to calculate time difference in minutes, using DateDiff()

 

[jpl458]

PMFJI:

Look again at MajP's post #9. He illustrated the most appropriate way to calculate time difference in minutes, using DateDiff()

Are you saying that you can't directly subtract the value in a textbox from the value in another textbox? I am trying this:

Me.Durationtb = DateDiff("n", Me.endtime, Me.starttime)

I get the error "You can't assign a value to this object"

I keep getting answers that involve either tables or the query grid, so, does that mean that Datediff will not work with textboxes, and that the only way is to create SQL? Doing in in one line of code seems simpler to me>

 

[GPGeorge]

Are you saying that you can't directly subtract the value in a textbox from the value in another textbox? I am trying this:

Me.Durationtb = DateDiff("n", Me.endtime, Me.starttime)

I get the error "You can't assign a value to this object"

I keep getting answers that involve either tables or the query grid, so, does that mean that Datediff will not work with textboxes, and that the only way is to create SQL? Doing in in one line of code seems simpler to me>

I'm confused now.

You posted:

Me.Durationtb = Me.starttime - Me.endtime

That's VBA syntax already. Me is used in a Sub or Function to refer to the form on which the code is executing. Sorry, but that's what I thought you wanted.

You use a different expression as the control source for text boxes on forms.

=DateDiff("n",[starttime ],[endtime])

 

[CJ_London]

that was suggested way back in post#4

 

[jpl458]

Your second image is not what I did. Relook at the DurationHoursMinutes calculated field.

View attachment 102937

Mine

View attachment 102938
Thus the error in your third image.


That is not how a query works. That is a dynamic column so nothing is stored or has to be forced to calculate. This is similar to a column in Excel that has a formula in it. If column C sums up columns A and B, you just enter values in A and B and column C gets dynamically updated.

The query you describe updates the underlying table for the data entry form, what I need to do is update a third (thus visible to the user) textbox. After trolling the web, and reading and trying stuff, I'm at this point, trying:

Durationtb = Format(DateDiff("n", Me.endtime, Me.starttime), "h:m:s")

but I get this error:

Plus, if I replace the name of the textbox with variable name, and debug. print it I get 9/1/2022 12:40:00 PM. I used your query as a clue, am I on the right track?

 

[jpl458]

I'm confused now.

You posted:



That's VBA syntax already. Me is used in a Sub or Function to refer to the form on which the code is executing. Sorry, but that's what I thought you wanted.

You use a different expression as the control source for text boxes on forms.

=DateDiff("n",[starttime ],[endtime])

There are three controls on the form, starttime, endtime, and durationtb, so I'm thinking that this should be close to what I need.

Me.durationtb =DateDiff("n",[starttime ],[endtime])

That piece pf code I was running out of the Lostfocus event on endtime. Forgive my ignorance but can you further explain what you mean by "control source" in your line 'You use a different expression as the control source for text boxes on forms. ( For me this problem is starting to take the shape of a large, white whale.)

 

[MajP]

See demo. I feel that I have explained this very clearly and posted screen shots of exactly how this should be done, so I am not sure of the issue you have in following it.
This shows doing it in a query or doing it in a calculated field.

 

[MajP]

Me.durationtb =DateDiff("n",[starttime ],[endtime])

No. That is VBA. In a calculated control you use expressions. This has been shown in multiple threads going back to #4. Please follow what we are posting.
A calculated control is an Expression not VBA. ME is used in a VBA class module to reference the current instance of the class

=DateDiff("n",[starttime ],[endtime])

 

[MajP]

The query you describe updates the underlying table for the data entry form, what I need to do is update a third (thus visible to the user) textbox. After trolling the web, and reading and trying stuff, I'm at this point, trying

The visible field is bound to the queries calculated column. See demo.
But if you bothered to read what I wrote it also provides the method to build this in a calculated control.

 

[MajP]

Me.Durationtb = Me.starttime - Me.endtime

This may or may not give you what you want. Probably not. Date/Time is stored as the amount of days since 31 Dec 1899 (integer part) and the time is the fraction of a total day. Whatever you see, is just a format applied to this value.
So
9/1/2022 2:16:55 PM
is stored as 44805.5950810185
44805 days since 31 Dec 1899
.595508... of the next day. (.5 is 12 noon, .75 is 6pm, etc)

If you do the above subtraction you will get a decimal value. Example: 2 minutes elapsed time will be returned as .00139. See demo. That is because 2 mins / 1440 mins in a day = .0138.