DateDiff

[Guest]

If DateDiff("n",[START_MK],[STOP_MK]) returns the elapsed time in Minutes,
what is the character to use for returning a general time elapsed between the
two dates in Hours AND Minutes (3:48) ?? Thanks. I tried looking in Access
help....must not be looking hard enough...

TIA

 

[Guest]

Hi.

Unfortunately, what you are looking for isn't in the Help files. The
DateDiff( ) function returns a Variant of subtype Long. Therefore, you can
use the DateDiff( ) function to return either hours or minutes, but not both
at the same time. The solution is to run the function twice, once to
retrieve the number of hours elapsed and again to retrieve the number of
minutes elapsed. Then use modulus division to calculate just the number of
minutes since the last hour of elapsed time (not the total amount of minutes
elapsed as retrieved by the DateDiff( ) function). Then format these values
into a string value to display or store in a table. Here's an example:

Dim lMins As Long
Dim lHrs As Long

lHrs = DateDiff("h", [START_MK], [STOP_MK])
lMins = DateDiff("n", [START_MK], [STOP_MK])
Me!txtElapsedTime.Value = lHrs & ":" & (lMins Mod 60)

where "txtElapsedTime" is a text box to display the elapsed time in hours
and minutes.

So the SQL expression for this calculated field in your query would look
like this:

(DateDiff("h", [START_MK], [STOP_MK]) & ":" & (DateDiff("n", [START_MK],
[STOP_MK]) Mod 60)) AS ElapsedTime

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.

 

[John Spencer (MVP)]

Hmm, I think that your generally correct, but that this particular solution will
at times give erroneous answers, since the DateDiff function counts the number
of boundaries crossed. So between 10:59 and 11:01 it would report an entire
hour had passed. Plus if you want leading zeroes on the minutes you need to
format it.

DateDiff("n",Start_MK,Stop_Mk)\60 & ":" &
Format(DateDiff("n",Start_MK,Stop_mk) Mod 60,"00")

Note that if you want to get minutes accurately, you may have to get seconds
elapsed and perform arithmetic on that similar to the math done to get the hours
- since 10:58:58 to 10:59:01 will return 1 Minute (number of minute boundaries crossed).

Hi.

Unfortunately, what you are looking for isn't in the Help files. The
DateDiff( ) function returns a Variant of subtype Long. Therefore, you can
use the DateDiff( ) function to return either hours or minutes, but not both
at the same time. The solution is to run the function twice, once to
retrieve the number of hours elapsed and again to retrieve the number of
minutes elapsed. Then use modulus division to calculate just the number of
minutes since the last hour of elapsed time (not the total amount of minutes
elapsed as retrieved by the DateDiff( ) function). Then format these values
into a string value to display or store in a table. Here's an example:

Dim lMins As Long
Dim lHrs As Long

lHrs = DateDiff("h", [START_MK], [STOP_MK])
lMins = DateDiff("n", [START_MK], [STOP_MK])
Me!txtElapsedTime.Value = lHrs & ":" & (lMins Mod 60)

where "txtElapsedTime" is a text box to display the elapsed time in hours
and minutes.

So the SQL expression for this calculated field in your query would look
like this:

(DateDiff("h", [START_MK], [STOP_MK]) & ":" & (DateDiff("n", [START_MK],
[STOP_MK]) Mod 60)) AS ElapsedTime

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.

If DateDiff("n",[START_MK],[STOP_MK]) returns the elapsed time in Minutes,
what is the character to use for returning a general time elapsed between the
two dates in Hours AND Minutes (3:48) ?? Thanks. I tried looking in Access
help....must not be looking hard enough...

TIA