textbox time calculation

[JohnE]

Hello. I have 2 textboxes on a form (StartTime and EndTime) that I need to
calculate out how much time elapsed between them. This goes into another
textbox to display the difference. The form's source comes from a query. In
the qry I added the extra field and the extra field and bound it to the form
field. Below is the qry that the form uses. An example is if I use 12:36
start time and 14:30 end time, the form field shows 0.08. What I would like
to show is the number of minutes between the 2 short times.

SELECT tblProjectWorkTime.ProjectWorkTimeID,
tblProject.ProjectName,
tblProjectWorkTime.ProjectWorkTimeDate,
tblProjectWorkTime.ProjectWorkStartTime,
tblProjectWorkTime.ProjectWorkEndTime,
tblProjectWorkTime.ProjectWorkTimeDescription,
tblProject.ProjectID,
tblProjectWorkTime.ProjectWorkTimeArchived,
[ProjectWorkEndTime]-[ProjectWorkStartTime] AS TotalAmount
FROM tblProject INNER JOIN tblProjectWorkTime ON tblProject.ProjectID =
tblProjectWorkTime.ProjectID
WHERE (((tblProject.ProjectID)=[Forms]![frmProject]![txtProjectID]) AND
((tblProjectWorkTime.ProjectWorkTimeArchived)=No))
ORDER BY tblProjectWorkTime.ProjectWorkTimeDate DESC;

If anyone can steer me in the right direction, thanks for doing so.
*** John

 

[Douglas J Steele]

Under the covers, Access stores Date/Times as 8 byte floating point numbers,
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and the decimal portion represents the time as a fraction
of a day.

Use

DateDiff("n", [ProjectWorkEndTime], [ProjectWorkStartTime]) AS TotalAmount

instead of

ProjectWorkEndTime]-[ProjectWorkStartTime] AS TotalAmount

Note that this won't work if the times are on the same day.

 

[JohnE]

The bad news is the times needing the calculation are in the same day. But
the good news is I got it figured out.
Thanks for the reply. The info is being kept for future reference.
*** John

Under the covers, Access stores Date/Times as 8 byte floating point numbers,
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and the decimal portion represents the time as a fraction
of a day.

Use

DateDiff("n", [ProjectWorkEndTime], [ProjectWorkStartTime]) AS TotalAmount

instead of

ProjectWorkEndTime]-[ProjectWorkStartTime] AS TotalAmount

Note that this won't work if the times are on the same day.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello. I have 2 textboxes on a form (StartTime and EndTime) that I need to
calculate out how much time elapsed between them. This goes into another
textbox to display the difference. The form's source comes from a query. In
the qry I added the extra field and the extra field and bound it to the form
field. Below is the qry that the form uses. An example is if I use 12:36
start time and 14:30 end time, the form field shows 0.08. What I would like
to show is the number of minutes between the 2 short times.

SELECT tblProjectWorkTime.ProjectWorkTimeID,
tblProject.ProjectName,
tblProjectWorkTime.ProjectWorkTimeDate,
tblProjectWorkTime.ProjectWorkStartTime,
tblProjectWorkTime.ProjectWorkEndTime,
tblProjectWorkTime.ProjectWorkTimeDescription,
tblProject.ProjectID,
tblProjectWorkTime.ProjectWorkTimeArchived,
[ProjectWorkEndTime]-[ProjectWorkStartTime] AS TotalAmount
FROM tblProject INNER JOIN tblProjectWorkTime ON tblProject.ProjectID =
tblProjectWorkTime.ProjectID
WHERE (((tblProject.ProjectID)=[Forms]![frmProject]![txtProjectID]) AND
((tblProjectWorkTime.ProjectWorkTimeArchived)=No))
ORDER BY tblProjectWorkTime.ProjectWorkTimeDate DESC;

If anyone can steer me in the right direction, thanks for doing so.
*** John

 

[Douglas J Steele]

Actually, that was a typo on my part. I meant to say "Note that this won't
work if the times are NOT on the same day."

Glad you got it working.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The bad news is the times needing the calculation are in the same day. But
the good news is I got it figured out.
Thanks for the reply. The info is being kept for future reference.
*** John

Under the covers, Access stores Date/Times as 8 byte floating point numbers,
where the integer portion represents the date as the number of days relative
to 30 Dec, 1899, and the decimal portion represents the time as a fraction
of a day.

Use

DateDiff("n", [ProjectWorkEndTime], [ProjectWorkStartTime]) AS TotalAmount

instead of

ProjectWorkEndTime]-[ProjectWorkStartTime] AS TotalAmount

Note that this won't work if the times are on the same day.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello. I have 2 textboxes on a form (StartTime and EndTime) that I

need
to

calculate out how much time elapsed between them. This goes into another
textbox to display the difference. The form's source comes from a

query.
In

the qry I added the extra field and the extra field and bound it to

the
form

field. Below is the qry that the form uses. An example is if I use 12:36
start time and 14:30 end time, the form field shows 0.08. What I

would
like

to show is the number of minutes between the 2 short times.

SELECT tblProjectWorkTime.ProjectWorkTimeID,
tblProject.ProjectName,
tblProjectWorkTime.ProjectWorkTimeDate,
tblProjectWorkTime.ProjectWorkStartTime,
tblProjectWorkTime.ProjectWorkEndTime,
tblProjectWorkTime.ProjectWorkTimeDescription,
tblProject.ProjectID,
tblProjectWorkTime.ProjectWorkTimeArchived,
[ProjectWorkEndTime]-[ProjectWorkStartTime] AS TotalAmount
FROM tblProject INNER JOIN tblProjectWorkTime ON tblProject.ProjectID =
tblProjectWorkTime.ProjectID
WHERE (((tblProject.ProjectID)=[Forms]![frmProject]![txtProjectID]) AND
((tblProjectWorkTime.ProjectWorkTimeArchived)=No))
ORDER BY tblProjectWorkTime.ProjectWorkTimeDate DESC;

If anyone can steer me in the right direction, thanks for doing so.
*** John