Calculation in Access 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 fields in a table:
Length of Tie In
Completed Date
Review Date

I want the Review Date to be the same as I have created in Form view =[Date
Of Completion]+[Length of Tie In1]*365-90

Does anyone know how I can do this in a Table please?
 
Create a query based on your table. With the query in desugn view, click on
the Type Of Query button in the menu at the top of the screen and chabge the
query to an Update query. Under the Review Date field, type the following
expression in Update To:
[Completed Date] + [Length Of Tie In] * 365 - 90

Question --
Do you want to subtract 90 from 365 before multiplying or after? If before,
you need (365-90).

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
Create a query based on your table. With the query in desugn view, click on
the Type Of Query button in the menu at the top of the screen and chabge the
query to an Update query. Under the Review Date field, type the following
expression in Update To:
[Completed Date] + [Length Of Tie In] * 365 - 90

Question --
Do you want to subtract 90 from 365 before multiplying or after? If before,
you need (365-90).
Click to expand...
Oh boy! The question remains... just how long will this change of
stripes last?

Snipped retarded continued used of ad.
 
Steve,
This has also worked fine.
In answer to your question, i wanted the formula exactly how it was.
Once again, many thanx for your help

Steve said:
Create a query based on your table. With the query in desugn view, click on
the Type Of Query button in the menu at the top of the screen and chabge the
query to an Update query. Under the Review Date field, type the following
expression in Update To:
[Completed Date] + [Length Of Tie In] * 365 - 90

Question --
Do you want to subtract 90 from 365 before multiplying or after? If before,
you need (365-90).

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)


Dave said:
I have 3 fields in a table:
Length of Tie In
Completed Date
Review Date

I want the Review Date to be the same as I have created in Form view
=[Date
Of Completion]+[Length of Tie In1]*365-90

Does anyone know how I can do this in a Table please?
Click to expand...
Click to expand...
 
Steve,
This has also worked fine.
In answer to your question, i wanted the formula exactly how it was.
Once again, many thanx for your help

Steve said:
Create a query based on your table. With the query in desugn view, click on
the Type Of Query button in the menu at the top of the screen and chabge the
query to an Update query. Under the Review Date field, type the following
expression in Update To:
[Completed Date] + [Length Of Tie In] * 365 - 90
Click to expand...
Click to expand...

Just one concern - you want to IGNORE leap years, so that the field will slip
to one calendar day earlier for every four years in the Length Of Tie In?

If you want to add that many years and then subtract 90 days from the result,
consider using the DateAdd function:

DateAdd("yyyy", [Length Of Tie In], [Completed Date]) - 90

This will add (say) 10 years to the value in Completed Date, giving the same
calendar date ten years hence regardless of leap years, and then subtract 90
days from that result.

John W. Vinson [MVP]
 
That makes the assumption that Length of Tie In is an INTEGER field. I
was about to post the same advice until I realized that Length of Tie In
could be something like 2.5

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Steve,
This has also worked fine.
In answer to your question, i wanted the formula exactly how it was.
Once again, many thanx for your help

Steve said:
Create a query based on your table. With the query in desugn view, click on
the Type Of Query button in the menu at the top of the screen and chabge the
query to an Update query. Under the Review Date field, type the following
expression in Update To:
[Completed Date] + [Length Of Tie In] * 365 - 90
Click to expand...
Click to expand...

Just one concern - you want to IGNORE leap years, so that the field will slip
to one calendar day earlier for every four years in the Length Of Tie In?

If you want to add that many years and then subtract 90 days from the result,
consider using the DateAdd function:

DateAdd("yyyy", [Length Of Tie In], [Completed Date]) - 90

This will add (say) 10 years to the value in Completed Date, giving the same
calendar date ten years hence regardless of leap years, and then subtract 90
days from that result.

John W. Vinson [MVP]
Click to expand...
 
Back
Top