DateDiff Problems

[Melissa Babcock via AccessMonster.com]

Hello all,

I am trying like crazy to set the default value of Field "ElapsedTime" with

DateDiff("n",[EndDateTime], [StartDateTime]) - ([StartDateTime] <
[EndDateTime]) * 1440

but I keep getting this stupid error

"The database engine does not recognize either the field 'EndDateTime' in a
validation expression, or the default value in the table 'Log' "

Can anyone help me with this ppplease

Missy

 

[Duane Hookom]

You can't set default values for fields that reference other fields. There
normally isn't good reason to store a value that and be calculated.

 

[John Vinson]

Hello all,

I am trying like crazy to set the default value of Field "ElapsedTime" with

DateDiff("n",[EndDateTime], [StartDateTime]) - ([StartDateTime] <
[EndDateTime]) * 1440

but I keep getting this stupid error

"The database engine does not recognize either the field 'EndDateTime' in a
validation expression, or the default value in the table 'Log' "

Can anyone help me with this ppplease

Missy

Where are you putting this expression? You *cannot* set a Table
Default value which depends on an existing field. And where are
[StartDateTime] and [EndDateTime] to be found?

I'm *GUESSING* that you're attempting to store the Elapsed Time in a
table. Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.


John W. Vinson[MVP]

 

[Melissa Babcock via AccessMonster.com]

Hmm, well my goal was to have the DB calculate the elapsed time and plug the
value in. [StartDateTime] and [EndDateTime] are fields in the same table with
[ElapsedTime], I didnt want to have to edit the source code that inputs
[StartDateTime] and [EndDateTime] if I didnt have to.

Missy

 

[John Vinson]

Hmm, well my goal was to have the DB calculate the elapsed time and plug the
value in. [StartDateTime] and [EndDateTime] are fields in the same table with
[ElapsedTime], I didnt want to have to edit the source code that inputs
[StartDateTime] and [EndDateTime] if I didnt have to.

Missy

Well, then, you should NOT be storing ElapsedTime in this table or any
other table. It is not necessary to store it, and in fact it's a bad
idea to try.

Simply store StartDateTime and EndDateTime in your Table; calculate
ElapsedTime in a query using your expression (I'm not at all sure what
the 1440 bit gets you though). Base any Forms or Reports on this
query; you won't be able to edit ElapsedTime but then since it's
derived from stored data, you wouldn't want to.

John W. Vinson[MVP]