DateDiff Problems

  • Thread starter Thread starter Melissa Babcock via AccessMonster.com
  • Start date Start date
M

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
 
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.
 
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
Click to expand...

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]
 
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
 
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
Click to expand...

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]
 
Back
Top