Date/Time Calculation

[Shaun Harwood]

Hello all

I seem to be getting nowhere with this issue - I'm sure it is not that
complex so here goes. I want to create a table with three fields - two
date/time types (Start_Date & Stop_Date) into which I enter dates. The
third field is a calculated value of the days between Start and Stop.
I have tried using Datediff but for some reasson it doesn't like basing the
value of my calculated days field on the result of a query calculated from
withing the same table. Hope this makes sense.

If anybody has a solution I would be grateful - this fast becoming an
obsession! - I am using MS Access 2003 on a Win XP SP2 platform.

Best Regards

Shaun H

[DFS]

Shaun,

To get the # of days between the dates, you can write a simple update query:

UPDATE table
SET DaysBetween = StopDate - StartDate
WHERE StartDate IS NOT NULL
AND StopDate IS NOT NULL;

If your DaysBetween field is set to Single or Double precision, that query
will return fractions of a day.


Shaun Harwood wrote:

Hello all

I seem to be getting nowhere with this issue - I'm sure it is not that
complex so here goes. I want to create a table with three fields -
two date/time types (Start_Date & Stop_Date) into which I enter
dates. The third field is a calculated value of the days between
Start and Stop.
I have tried using Datediff but for some reasson it doesn't like
basing the value of my calculated days field on the result of a query
calculated from withing the same table. Hope this makes sense.

If anybody has a solution I would be grateful - this fast becoming an
obsession! - I am using MS Access 2003 on a Win XP SP2 platform.

Best Regards

Shaun H

[Shaun Harwood]

Thankyou - that does the job perfectly - now is there a way to run the
update query automatically (maybe when you start access) so the field will
update itself - or do I have to run the query each time.

Thanks

[RoyVidar]

Shaun Harwood wrote in message
<Xn**********************************@207.35.177.1 35> :

Thankyou - that does the job perfectly - now is there a way to run
the update query automatically (maybe when you start access) so the
field will update itself - or do I have to run the query each time.

Thanks

I would recommend that you rethink this strategy. It is considered much
better to calculate values on the fly, when you need them, in stead of
storing the calculated values.

When choosing to "break the rules", as in storing calculated values,
you
expose yourself to the calculated risk of your updates failing,
creating
a situation where your calculated values are out of sync with the
fields
they are supposed to be derived from.

--
Roy-Vidar