Datediff - make results permanent in table?

[Guest]

Hi,

I'm missing something really basic, I just can't figure out what. I need to
calculate the difference in time between 2 fields. I've figured out the
formula:

DateDiff("s",[SiteData]![TranscriptionDate],[BirdData]![Autotime])

and it works great when I place the expression under 'Field' in a select
query - the answers all appear as they should when I run the query. But I
need the answers to be pasted permanently in a field in the table, which a
select query won't do. I've tried update queries, but I can't figure out how
to make the results of the calculations appear in the table. I need to do
subsequent calculations on the answers to the first calculation, so I need
them to appear in the table.

If you can be explicit with your answer that would be great - i.e. where to
place the above formula, in what type of query, if I'm missing any operators,
etc.

Thanks!
Theresa

 

[Duane Hookom]

If you really think you need to store a value that can be calculated (rarely
necessary), use an update query. You query must be updateable. It is
impossible to tell what your query SQL might be or what issues you had when
you attempted this.

Do you have some table and field names to share as well as the primary and
foreign keys if more than one table is involved?

 

[Tom Ellison]

Dear Theresa:

First, it is almost always a bad thing to store derivable values. Just
derive them when you need them.

If you insist on storing it, take the existing SELECT query and have it
transformed into an UPDATE. Then select the destination column for this
calculated value.

Tom Ellison

 

[Guest]

Thanks to both of you - it worked great. I understand the risks of storing
the derived values...but honestly, it's necessary. But I'll file away the
warning for future reference.

Cheers,
Theresa