Update query won't update table

[bonesie]

I have a simple table with 3 fields.
StartTime
EndTime
ElapsedTime
Each field is defined as a date/time datatype.
StartTime and EndTime are manually entered.
I'm trying to use the DateDiff() function in an update query to populate the ElapsedTime field with the number of seconds elapsed between StartTime and EndTime.
I put the ElapsedTime field on the query grid, convert the query to an update query and on the "Update To" row of the ElipsedTime column, I've entered: DateDiff("s", [StartTime], [EndTime])
When I run the query, I don't get any errors, but the ElapsedTime field in the table does not update.
Can you tell me what I am doing wrong?

[Rod]

The probable cause is that Elapsed Time is not a Date/Time data type. Change to a numeric data type and it should work. (It may be helpful if you published the SQL generated from the design grid just to make sure all's OK.) I recommend you read up on how dates and times are stored in a Date/Time data type.

The purists will now also ask why you are storing all three attributes. Knowing two of the values you can calculate the third and derived (or calculated) values should not be stored in a normalised database unless there is a very good reason for doing so.

[bonesie]

Thanks for responding, Rod.

I've tried changing the ElapsedTime field to number, date/time, and text datatypes. All run without any errors, but neither updates the field.

Here is the sql generated: UPDATE Table1 SET Table1.dtmElapsedTime = DateDiff("s",[dtmStartTime],[dtmEndTime]);

Normally I would just get the derived value with a query, but someone has asked me how to update table with the elapsed time. At the time, it seemed like a perfectly straightforward request. But when I tried to update the table with the corresponding elapsed time, I found that I couldn't do it and even worse, couldn't figure out why.

[Rod]

Can see nothing wrong with the SQL. I have even reproduced your situation and it works for me.

Just a thought - you are clicking the 'Run' button and not the 'View' button when testing?

[bonesie]

Hi, Rod.

When all else fails, reboot.

After I restarted the pc and set the data type to text, the update is working. Not sure what was going on, but thanks so much for your help.

[Rod]

A data type of text is not such a good idea!

[bonesie]

I have changed the elapsed time data type to a number to store seconds and it is working.

Thanks again, Rod.