store DateDiff as a calcuable number for queries/reporting

I'm trying to setup a usage log db that calculates the minutes. I've got the
setup as follows:

Primary Key (autonumber)
TheDate (Date/Time)
TimeOn (Date/Time)
TimeOff (Date/Time)
TtlMinutesperEntry (Number)

On the form, I changed the control source for the "TtlMintuesperEntry" to
the formula:

=DateDiff("n",[TimeOn],[TimeOff])

which correctly calculates the minutes and shows the value in the fieldbox.
However, in the Table, Query, and Report, the value that is returned for
that formula-in-field is not showing up for calculations. How do I get this
setup to do so?

Thank you in advance for your any help you can provide.

"sk" wrote:

I'm trying to setup a usage log db that calculates the minutes. I've got the
setup as follows:

Primary Key (autonumber)
TheDate (Date/Time)
TimeOn (Date/Time)
TimeOff (Date/Time)
TtlMinutesperEntry (Number)

On the form, I changed the control source for the "TtlMintuesperEntry" to
the formula:

=DateDiff("n",[TimeOn],[TimeOff])

which correctly calculates the minutes and shows the value in the fieldbox.
However, in the Table, Query, and Report, the value that is returned for
that formula-in-field is not showing up for calculations. How do I get this
setup to do so?

You don't.

Storing such data does three things: wastes disk space; wastes time (a
DateDiff is MUCH faster than a disk read); and risks invalid data. If one of
the underlying fields changes, you now have a duration stored in your table
WHICH IS WRONG, with no easy way to detect that fact.

Just store the time points, and calculate the duration on the fly, in a
Query calculated field or in the control source of a form or report textbox.

Sorry John, I think you've misunderstood me a bit.

I'm doing as you've suggested. The "on-the-fly" calculation is showing up
in the form for that record no problem. The problem is, I can't get that
value given for each record to show up in the reports when I go to build a
query or report for a group of records.

Yet when I've created other dbs that do calculations, I've been able to get
the returned value to show in each record in a Query and print a report on it
as well...however, the difference here is I've never done a time calculation
before.

Your thoughts?

"John Vinson" wrote:

"sk" wrote:

I'm trying to setup a usage log db that calculates the minutes. I've got the
setup as follows:

Primary Key (autonumber)
TheDate (Date/Time)
TimeOn (Date/Time)
TimeOff (Date/Time)
TtlMinutesperEntry (Number)

On the form, I changed the control source for the "TtlMintuesperEntry" to
the formula:

=DateDiff("n",[TimeOn],[TimeOff])

which correctly calculates the minutes and shows the value in the fieldbox.
However, in the Table, Query, and Report, the value that is returned for
that formula-in-field is not showing up for calculations. How do I get this
setup to do so?

You don't.

Storing such data does three things: wastes disk space; wastes time (a
DateDiff is MUCH faster than a disk read); and risks invalid data. If one of
the underlying fields changes, you now have a duration stored in your table
WHICH IS WRONG, with no easy way to detect that fact.

Just store the time points, and calculate the duration on the fly, in a
Query calculated field or in the control source of a form or report textbox.

The common approach is to calculate the DateDiff() in your report, or do it
in the query that underlies your report and use it in the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"sk" wrote in message
...
Sorry John, I think you've misunderstood me a bit.

I'm doing as you've suggested. The "on-the-fly" calculation is showing up
in the form for that record no problem. The problem is, I can't get that
value given for each record to show up in the reports when I go to build a
query or report for a group of records.

Yet when I've created other dbs that do calculations, I've been able to
get
the returned value to show in each record in a Query and print a report on
it
as well...however, the difference here is I've never done a time
calculation
before.

Your thoughts?

"John Vinson" wrote:

"sk" wrote:

I'm trying to setup a usage log db that calculates the minutes. I've
got the
setup as follows:

Primary Key (autonumber)
TheDate (Date/Time)
TimeOn (Date/Time)
TimeOff (Date/Time)
TtlMinutesperEntry (Number)

On the form, I changed the control source for the "TtlMintuesperEntry"
to
the formula:

=DateDiff("n",[TimeOn],[TimeOff])

which correctly calculates the minutes and shows the value in the
fieldbox.
However, in the Table, Query, and Report, the value that is returned
for
that formula-in-field is not showing up for calculations. How do I
get this
setup to do so?

You don't.

Storing such data does three things: wastes disk space; wastes time (a
DateDiff is MUCH faster than a disk read); and risks invalid data. If one
of
the underlying fields changes, you now have a duration stored in your
table
WHICH IS WRONG, with no easy way to detect that fact.

Just store the time points, and calculate the duration on the fly, in a
Query calculated field or in the control source of a form or report
textbox.

On Mon, 13 Apr 2009 16:28:03 -0700, sk wrote:

Sorry John, I think you've misunderstood me a bit.

I'm doing as you've suggested. The "on-the-fly" calculation is showing up
in the form for that record no problem. The problem is, I can't get that
value given for each record to show up in the reports when I go to build a
query or report for a group of records.

Use the expression - again - on the Report, or in a Query.

Yet when I've created other dbs that do calculations, I've been able to get
the returned value to show in each record in a Query and print a report on it
as well...however, the difference here is I've never done a time calculation
before.

If you do the calculation *IN A QUERY* (rather than in the control source of a
textbox on a form) then you can base the form, or a report, or six forms and
eighteen reports g, on that query and the calculation will be reflected on
all of them.

I think the problem is that you're assuming that a calculation done in the
control source of a form textbox will carry over. It won't.
--

John W. Vinson [MVP]