Hi,

Can anyone please help, this is doin my head in.. I have a calculated date field on a form... can i send this information to tha main table... i've tried all sorts.

the calculation is =DateAdd("m",[Combined Inspection and test frequency],[Combined Inspect & test LastServiceDate])

and i want it information to go to the main table in "Combined NextServiceDate" .. I know its not normally done but i need to for a query to work... I know away is to to it useing code with the after update method but i ain't that good with code.. understand it when i c it,,,

can it be done .. if so how

Please help

Pos,

Any value that is calculated should rarely be stored in a table. You would be better off writing a query that includes this calculation and then use that query as the basis for any further queries you need to run.

HTH
SteveA http://www.access-programmers.co.uk/ubb/smile.gif

Hmm

Both the inputs in the calculation vary from record to record.. i have put the calculation into a query but did not give the result needed..

Could anyone give me a top tip in the code i need to write in afterupdate

Ta Pos,

Experienced Members don't give advice on what not to do without good reason. If your query is not displaying the results you require then it's not structured correctly. Post the SQL so that someone can help you on the correct way.

Here u go..this is the query sql

SELECT [All of MCF].[Unit Serial number], [All of MCF].[Site (General Location)], [All of MCF].Location, [All of MCF].Position, [All of MCF].[Item Description], [All of MCF].[Formal Visual NextServiceDate], [All of MCF].[Combined Inspect & test NextServiceDate]
FROM [All of MCF]
WHERE ((([All of MCF].[Formal Visual NextServiceDate]) Between Date() And DateAdd("m",1,Date())) AND (([All of MCF].[Combined Inspect & test NextServiceDate]) Between Date() And DateAdd("m",1,Date())))
ORDER BY [All of MCF].[Site (General Location)];

The two independant nextservicedate are not filled in as the input comes from the above calculation on a form with i need to send to the table

Pos,

[This message has been edited by Pos (edited 01-13-2002).]

Standard practice is to store the Last (most recent) service date and calculate the next service date. The calculation for next service would then entail adding the service interval to the last service date and then comparing the resulting date to the current date plus one month.

Hi,

Hi there,

Thanks for answering.. ...Will try to explain why i need what , i hope simple terms..

I service a large amount of equipment. I need to find out each month what is due to be done or I get it in the neck.. I have in the table

A The last time I did it
B interval of servicing
C when its next due

A B C
A=Feb/01 B=12 C=Feb/02
A=Nov/01 B=3 C=Jan/02

A & B vary and i want to send the calculation to C. I have calculated C but in a form.
This being C=DateAdd("m",[B],[A]) That works fine on the form where people input the data to. I know I could enter data into C manually but I'm trying to make it idiot proof. had problem with people trying to add months!!!!!

Now i want in a query to find out what is due say this month which I have used the expression <Date() in the C part of the query.... cause not data in the form has been returned to C in the table the query don't pick it up..

I have found out to use the afterupdate field in the properties but am unable to sus the codeing out..

Cheers Pos

[This message has been edited by Pos (edited 01-14-2002).]

[This message has been edited by Pos (edited 01-14-2002).]

You do NOT need to store this date. Calculate it in the query. In a Field cell of the query (open it in design view), place:

NextDate http://www.access-programmers.co.uk/ubb/biggrin.gifateAdd("m",[B],[A])

and in the Criteria cell of this new column place:

< DateAdd("m",1,Date())

Hi,

Well a great big thankyou goes to everyone helping me... It worked, a simple conclusion..

Learnt laods doin this and I now know where to come.

Again Thanks

Hi again,

Again a big thankyou but I need some more help... The problem is the calculations work brill in access 2002 ..But when i convert it to access 97, need to use it at work and they are not up todate, the calculation in the critea field causes an expression error... I've tried lookin in the help and using the examples but no matter what i enter in the critea field an error occurs... did read about a subquery i was wondering if that was the answer.

What would be the expression to use with access97.

Thanks Pos,

Will close this now as its gone on to being a query question

Cheers Pos,