Adding Minutes

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

On my form I have detailBeginTime and detailEndTime controls that contain
Medium Time format. To calculate actual minutes, I use the formula in FIGURE
1 and that works fine. My problem is in my form footer. I need a sum of the
minutes being calculated by FIGURE 1 formula on main form.

If the name of my calculated field in FIGURE 1 is called MINUTES, how can I
convert FIGURE 1 results to actual numeric value of minutes that can be
summed up on the form footer? FIGURE 2 shows my failed attempt. FIGURE 3
shows a sample record.


FIGURE 1
Format([detailBeginTime]-1-[detailEndTime],"Short Time")


FIGURE 2
Int(Format([detailBeginTime]-1-[detailEndTime],"Short
Time"))/60


FIGURE 3

detailBeginTime detailEndTime Minutes MyProblemControl*
6:00 AM 6:45 AM 45 .75


* shows what correct value should be
 
I made 1 mistake in description, the 45 shown below is actually shown as
00:45 in Short Time format.
 
Use the DateDiff function to calculate the difference between two times:

NumberOfMinutes = DateDiff("n", [detailBeginTime], [detailEndTime])

Time is stored as a fraction of a 24-hour day.

Then you can use the above expression in your summing equation as well.
 
Your formula does convert to a number of minutes in detail section, but I
keep getting an error on form footer when trying to sum the minutes.

Do I need to convert below to a different type?

=Sum([NumberOfMinutes])


Ken Snell said:
Use the DateDiff function to calculate the difference between two times:

NumberOfMinutes = DateDiff("n", [detailBeginTime], [detailEndTime])

Time is stored as a fraction of a 24-hour day.

Then you can use the above expression in your summing equation as well.

--
Ken Snell
<MS ACCESS MVP>

Scott said:
On my form I have detailBeginTime and detailEndTime controls that contain
Medium Time format. To calculate actual minutes, I use the formula in FIGURE
1 and that works fine. My problem is in my form footer. I need a sum of the
minutes being calculated by FIGURE 1 formula on main form.

If the name of my calculated field in FIGURE 1 is called MINUTES, how
Click to expand...
can
I
convert FIGURE 1 results to actual numeric value of minutes that can be
summed up on the form footer? FIGURE 2 shows my failed attempt. FIGURE 3
shows a sample record.


FIGURE 1
Format([detailBeginTime]-1-[detailEndTime],"Short Time")


FIGURE 2
Int(Format([detailBeginTime]-1-[detailEndTime],"Short
Time"))/60


FIGURE 3

detailBeginTime detailEndTime Minutes MyProblemControl*
6:00 AM 6:45 AM 45 .75


* shows what correct value should be
Click to expand...
Click to expand...
 
You must repeat the expression in the footer...you can't use the name of the
calculated expression as a shortcut:

=Sum(DateDiff("n", [detailBeginTime], [detailEndTime]))

--
Ken Snell
<MS ACCESS MVP>


Scott said:
Your formula does convert to a number of minutes in detail section, but I
keep getting an error on form footer when trying to sum the minutes.

Do I need to convert below to a different type?

=Sum([NumberOfMinutes])


Ken Snell said:
Use the DateDiff function to calculate the difference between two times:

NumberOfMinutes = DateDiff("n", [detailBeginTime], [detailEndTime])

Time is stored as a fraction of a 24-hour day.

Then you can use the above expression in your summing equation as well.

--
Ken Snell
<MS ACCESS MVP>

Scott said:
On my form I have detailBeginTime and detailEndTime controls that contain
Medium Time format. To calculate actual minutes, I use the formula in FIGURE
1 and that works fine. My problem is in my form footer. I need a sum
Click to expand...
of
the
minutes being calculated by FIGURE 1 formula on main form.

If the name of my calculated field in FIGURE 1 is called MINUTES, how
Click to expand...
can
I
convert FIGURE 1 results to actual numeric value of minutes that can be
summed up on the form footer? FIGURE 2 shows my failed attempt. FIGURE 3
shows a sample record.


FIGURE 1
Format([detailBeginTime]-1-[detailEndTime],"Short Time")


FIGURE 2
Int(Format([detailBeginTime]-1-[detailEndTime],"Short
Time"))/60


FIGURE 3

detailBeginTime detailEndTime Minutes MyProblemControl*
6:00 AM 6:45 AM 45 .75


* shows what correct value should be
Click to expand...
Click to expand...
Click to expand...
 
it's a continuos form, surely there must be a way to add up the times.

any ideas greatly appreciated.


Ken Snell said:
You must repeat the expression in the footer...you can't use the name of the
calculated expression as a shortcut:

=Sum(DateDiff("n", [detailBeginTime], [detailEndTime]))

--
Ken Snell
<MS ACCESS MVP>


Scott said:
Your formula does convert to a number of minutes in detail section, but I
keep getting an error on form footer when trying to sum the minutes.

Do I need to convert below to a different type?

=Sum([NumberOfMinutes])


Ken Snell said:
Use the DateDiff function to calculate the difference between two times:

NumberOfMinutes = DateDiff("n", [detailBeginTime], [detailEndTime])

Time is stored as a fraction of a 24-hour day.

Then you can use the above expression in your summing equation as well.

--
Ken Snell
<MS ACCESS MVP>

On my form I have detailBeginTime and detailEndTime controls that contain
Medium Time format. To calculate actual minutes, I use the formula in
FIGURE
1 and that works fine. My problem is in my form footer. I need a sum of
the
minutes being calculated by FIGURE 1 formula on main form.

If the name of my calculated field in FIGURE 1 is called MINUTES,
Click to expand...
how
can
I
convert FIGURE 1 results to actual numeric value of minutes that can be
summed up on the form footer? FIGURE 2 shows my failed attempt.
Click to expand...
Click to expand...
FIGURE
3
shows a sample record.


FIGURE 1
Format([detailBeginTime]-1-[detailEndTime],"Short
Time")


FIGURE 2
Int(Format([detailBeginTime]-1-[detailEndTime],"Short
Time"))/60


FIGURE 3

detailBeginTime detailEndTime Minutes MyProblemControl*
6:00 AM 6:45 AM 45 .75


* shows what correct value should be
Click to expand...
Click to expand...
Click to expand...
 
Scott, Ken's suggestion to use DateDiff() is definately the way to go.

If you cannot get the expression to work correctly in your continuous form,
create a query with the calculated field Minutes. Once you have that
working, use the query as the RecordSource of your form, and you can sum the
Minutes field.

Details of how and why in article:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Scott said:
it's a continuos form, surely there must be a way to add up the times.

any ideas greatly appreciated.


Ken Snell said:
You must repeat the expression in the footer...you can't use the name of the
calculated expression as a shortcut:

=Sum(DateDiff("n", [detailBeginTime], [detailEndTime]))

--
Ken Snell
<MS ACCESS MVP>


Scott said:
Your formula does convert to a number of minutes in detail section,
Click to expand...
Click to expand...
but
I
keep getting an error on form footer when trying to sum the minutes.

Do I need to convert below to a different type?

=Sum([NumberOfMinutes])


Use the DateDiff function to calculate the difference between two times:

NumberOfMinutes = DateDiff("n", [detailBeginTime], [detailEndTime])

Time is stored as a fraction of a 24-hour day.

Then you can use the above expression in your summing equation as well.

--
Ken Snell
<MS ACCESS MVP>

On my form I have detailBeginTime and detailEndTime controls that
contain
Medium Time format. To calculate actual minutes, I use the formula in
FIGURE
1 and that works fine. My problem is in my form footer. I need a
Click to expand...
sum
of
the
minutes being calculated by FIGURE 1 formula on main form.

If the name of my calculated field in FIGURE 1 is called MINUTES, how
can
I
convert FIGURE 1 results to actual numeric value of minutes that
Click to expand...
can
be
summed up on the form footer? FIGURE 2 shows my failed attempt.
Click to expand...
FIGURE
3
shows a sample record.


FIGURE 1
Format([detailBeginTime]-1-[detailEndTime],"Short
Time")


FIGURE 2

Int(Format([detailBeginTime]-1-[detailEndTime],"Short
Time"))/60


FIGURE 3

detailBeginTime detailEndTime Minutes MyProblemControl*
6:00 AM 6:45 AM 45 ..75


* shows what correct value should be
Click to expand...
Click to expand...
Click to expand...
 
Back
Top