Adding Minutes

[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

 

[Scott]

I made 1 mistake in description, the 45 shown below is actually shown as
00:45 in Short Time format.

 

[Ken Snell]

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.

 

[Scott]

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])

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, 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

 

[Ken Snell]

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>

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])

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, 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

 

[Scott]

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

any ideas greatly appreciated.

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>

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])

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,

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

 

[Allen Browne]

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.

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

any ideas greatly appreciated.

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>

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])


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, 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