Calculating Dates using Update Query

[dhersch]

I am creating a database workflow that will automatically show dates that certain business requirements are due. I want to have these calculated values calculated automatically, which I can accomplish using a standard query. My problem is that these dates will need to be changed by users periodically to reflect timeline changes. These changes will then need to be saved permanently. Can an update query be able update only individual records in certain fields without affecting any other data?

I was hoping that there was a way that I could have these dates in my table calculated based off a standard input date on the same table. This way, I could easily have these calculated dates be changed by the user.

Am I missing the easiest way to accomplish this task?

 

[Norie]

Can an update query be able update only individual records in certain fields without affecting any other data?

This is possible just use criteria to select the records you want and include only the fields you want updated.

First try creating a select query to make sure that you get the records/fields you want.

 

[dhersch]

This seems as though it will be difficult. . . Is there any way to do a calculation in a table?

How does one select the current record with a select query?

 

[SydneyGeek]

Try a form displaying multiple records. You could create a Yes/No field called ToChange, which is displayed on the form.
The user checks the ones to update, and the Update query then filters for those records and makes the change. You could get it to prompt you for a date to insert.
Note: you'd need to make sure that ToChange was blanked out in records that don't require updating.

Denis

 

[dhersch]

The problem with this is that a user could want to make several updates to the auto dates that will not be consistent. For instance, they may want to add a week to a certain deadline date and take 2 days off of the same deadline for a different record. I can see this getting very tricky.


With this idea, would I need a check box to change each of the calculated fields? There will probably be around 8 calculated dates and I don't know how I will be able to program which date will need to be changed?

I was still hoping to be able to create a calculated field in a table, which although will not be the most efficient, will be simpler to manage on an ongoing basis. I know I can have default fields in a table, is it possible to create a default calculation?

 

[SydneyGeek]

Sorry, tables don't do calculations -- although, if you set up a form with an unbound calculation field you can use event code to push that value to a table field. Alternatively, you can use a combo box to push related data into any number of fields.

The main issue seems to be the variable update intervals -- not sure what the best approach would be there.

Denis

 

[Norie]

Are the "auto dates" generated from another date?

e.g. StartDate = 16 May 2004 => ExpectedEndDate = 16 June 2004

If they are and you are using a form you can populate the 'auto dates' by using the events (Exit, Update, Change etc.) of the original date.

The user could freely alter these dates and they could be bound to the fields.

Are you using a form?

 

[dhersch]

Thanks for the help!

I will be using a form for this.
All of the dates are generated from another date. However, would using this method allow a user to change a date and have it saved? For instance, if a user were to use an "update", would this change save over the automatically calculated date? This method seems as though it would be great.

I will look into events and see how they work. Ill let you know how it works out. I really appreciate your help.

 

[Norie]

Say you have a form with two textboxes bound to StartDate and ExpectedEndDate, call then txtStart and txtEnd.

In the exit event for txtStart put this code

Me.txtEnd = DateAdd("m", 6, Me.txtStart)

In this example when the user exits the start field the end field is set to start+6 months.

If the user doesn't like that he can change it.

Whatever the values end up they will only be 'saved' when the user moves to another record.

If the start and end dates have to follow some set of rules these could be implemented before the user leaves the record.