Calculating time difference in minutes

[The_Doc_Man]

@jpl458 - you are tripping over the difference between computation and presentation. You also did the computation backwards. Here is the basic VBA computation ...

Me.Durationtb = Me.endtime - me.starttime

Elapsed times always involve taking the larger time minus the smaller time, and for Access, any time after 30-Dec-1899 increases as the date gets later. The above sample would work correctly from VBA in the Form_Current event if [Durationtb] had a format property that would convert time to whatever units you wanted. You could also do this in VBA without formatting in the control properties.

Me.Durationtb = Format( ( Me.endtime - Me.starttime ), "HH:NN" )

If you wanted to not use VBA then use this expression as the .ControlSource of Me.Durationtb...

= Format( ( Me.endtime - Me.starttime ), "HH:NN" )

That would compute the difference for you without explicit VBA. It would be updated during each Form_Current event.

HOWEVER, if the difference exceeds 24 hours you will have issues because Access time routines do not allow conversion of times greater than one day using that particular method of formatting. You would have to "roll your own" formats for more than a day in minutes. If you wanted hours and minutes AND the hours could exceed 24, the best way is a two-step process. Is there a chance this difference could exceed 24 hours?

 

[jpl458]

@jpl458 - you are tripping over the difference between computation and presentation. You also did the computation backwards. Here is the basic VBA computation ...

Me.Durationtb = Me.endtime - me.starttime

Elapsed times always involve taking the larger time minus the smaller time, and for Access, any time after 30-Dec-1899 increases as the date gets later. The above sample would work correctly from VBA in the Form_Current event if [Durationtb] had a format property that would convert time to whatever units you wanted. You could also do this in VBA without formatting in the control properties.

Me.Durationtb = Format( ( Me.endtime - Me.starttime ), "HH:NN" )

If you wanted to not use VBA then use this expression as the .ControlSource of Me.Durationtb...

= Format( ( Me.endtime - Me.starttime ), "HH:NN" )

That would compute the difference for you without explicit VBA. It would be updated during each Form_Current event.

HOWEVER, if the difference exceeds 24 hours you will have issues because Access time routines do not allow conversion of times greater than one day using that particular method of formatting. You would have to "roll your own" formats for more than a day in minutes. If you wanted hours and minutes AND the hours could exceed 24, the best way is a two-step process. Is there a chance this difference could exceed 24 hours?

Not likely to exceed 24 hours, we're talking about 15 minutes or less. I knew that formatting was key, and I have been experimenting with the following.

Debug.Print Me.endtime
Debug.Print Me.starttime


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

I now get a number in the durationtb and will use your formatting suggestion. Is the a way to get minutes and second? Like "MM:SS5". I will try and find that. But thanks so much for the clear and direct post.

 

[jpl458]

@jpl458 - you are tripping over the difference between computation and presentation. You also did the computation backwards. Here is the basic VBA computation ...

Me.Durationtb = Me.endtime - me.starttime

Elapsed times always involve taking the larger time minus the smaller time, and for Access, any time after 30-Dec-1899 increases as the date gets later. The above sample would work correctly from VBA in the Form_Current event if [Durationtb] had a format property that would convert time to whatever units you wanted. You could also do this in VBA without formatting in the control properties.

Me.Durationtb = Format( ( Me.endtime - Me.starttime ), "HH:NN" )

If you wanted to not use VBA then use this expression as the .ControlSource of Me.Durationtb...

= Format( ( Me.endtime - Me.starttime ), "HH:NN" )

That would compute the difference for you without explicit VBA. It would be updated during each Form_Current event.

HOWEVER, if the difference exceeds 24 hours you will have issues because Access time routines do not allow conversion of times greater than one day using that particular method of formatting. You would have to "roll your own" formats for more than a day in minutes. If you wanted hours and minutes AND the hours could exceed 24, the best way is a two-step process. Is there a chance this difference could exceed 24 hours?

Huzza!!! It works. Thanks you again, really appreciate it.

 

[The_Doc_Man]

Is the a way to get minutes and second?

This format will give you hours, minutes, and seconds: "HH:NN:SS"

Be aware of the following features and requirements: The input must be a TIME or DATE/TIME value that represents less than one day but not less than 0 time. Access date formatting routines do not respond to fractions of a second. Since DATE variables are actually what is called a typecast of a DOUBLE, you could also feed in a DOUBLE and if it is between 0 and 1.0 (not inclusive), you would get a time in hours, minutes, and seconds. The difference between two date/time fields will also be compatible with variables either of DATE or DOUBLE data type.

 

[jpl458]

This format will give you hours, minutes, and seconds: "HH:NN:SS"

Be aware of the following features and requirements: The input must be a TIME or DATE/TIME value that represents less than one day but not less than 0 time. Access date formatting routines do not respond to fractions of a second. Since DATE variables are actually what is called a typecast of a DOUBLE, you could also feed in a DOUBLE and if it is between 0 and 1.0 (not inclusive), you would get a time in hours, minutes, and seconds. The difference between two date/time fields will also be compatible with variables either of DATE or DOUBLE data type.

I already tried that and it works. Thanks.

Just an aside question, is there any slick way to automatically version ACCESS apps as their being developed. or do you handle that manually and just use Save as when you want a new version. Just had a brain fade and lost a bit of code, just want to protect from loosing a lot.

Thanks again for your help

 

[The_Doc_Man]

There are 3rd-party products that will do a form of version control, but native Access doesn't do that. Instead, it gives you everything you need to do that by hand. I think that one big reason for lack of version control is that Access was originally designed for small businesses whereas formal version control systems tend to relevant in medium to large projects.

In my own large projects I built a history table with a version string, a couple of dates, and a child table in which I kept text fields to describe "fixes" and "upgrades" associated with a version number. The FE changed a lot so I made the BE have its own version. The place where I worked didn't allow auto-updaters (over-zealous IT dept.) so the FE would check the compatibility table that showed which versions of the FE would work with which versions of the BE. If there was a mismatch, the FE would stop and say "Update required." If they were compatible but not matching, the FE would say "Update suggested but not required yet."

 

[jpl458]

I am writing a stand alone app for my own use. Got my fingers crossed and lost some work. I am aware of GIT, but that's overkill for what I am doing. Just have to be careful.

 

[The_Doc_Man]

overkill for what I am doing.

Exactly the problem for MOST commercial code control packages when considering Access projects.

 

[GregDataReno]

I have 3 text boxes;

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



Any help would be greatly appreciated

Assuming the text boxes hold fields from a table or query or can be manually entered, and
assuming the values are held as text (or dates), then
the resultant text box could have this formula

=datediff("s",cvdate(me.starttime),cvdate(me.endtime)) ' = <n> seconds

CVDATE() will return a NULL value if either field does not represent a legitimate datetime value. The resulting field - if it's a text box - would then be an empty string ie. ""



Test these examples in the VBA IDE...

=VBA.datediff("n", now(),now()+1) ' = 1440 minutes ( the +1 defaults to 1 day ie 1440 minutes)
=VBA.datediff("d", now(),now()+1) ' = 1 day
=VBA.datediff("h", now(),now()+1) ' = 24 hours
=VBA.datediff("m", now(),now()+1) ' = 0 months
=VBA.datediff("s", now(),VBA.dateadd("s",10,now())) ' = 10 seconds