Cycle Time

[Dargo]

I know how to create and expression that calculates cycle time between 2 dates.

=DateDiff("d",[OVR CAPA 411 PDR Input Table]![Date Closed],[OVR CAPA 411 PDR Input Table]![Date Issued])

However, how do I save this calculation to a field in a table? I created a field in the table called "cycle time".

Also, I would like this to be an automatic function that as soon as someone inputs the "date issued" on the form it will calculate the cycle time from the date issued to "Date()". However, once the "Date Closed" info is entered it will calculate based upon that rather than Date().

I have tried to make an update query that I could launch with a macro when the form is opened. It would calculate cycle time based upon the two dates or if "date issued" was not null and "date closed" was null it would calculate the cycle time based upon today's date.

I can't seem to get the update query to work as planned. Any guidance would be appreciated.

[ibgreat]

It would be helful to see you UPDATE query you started to work on. Please post it.

Also, I would recommend using an event like AFTERUPDATE in the field for "date issued". From there we can look at your VBA if necessary.

[Dargo]

It would be helful to see you UPDATE query you started to work on. Please post it.

Also, I would recommend using an event like AFTERUPDATE in the field for "date issued". From there we can look at your VBA if necessary.

Thank you.

Let me start by stating what I want to happen....

Once "Date Issued" is entered, "Cycle Time" will be updated. If "Date Closed" is Null, "Cycle Time" would be based upon Date() minus "Date Issued.

If "Date Closed" is not Null, then "Cycle Time" is "Date Closed" minus "Date Issued".

The data entry is all done on a form and I would like to have the "Cycle Time" field automatically updated once "Date Issued" is entered. Also, I guess I need to have "Cycle Time" continuously update whenever the database is opened to keep it accurate as I would not be "updating" "Issued Date" on the Form.

Is it best to use a macro, update query, other?

I tried to select my action query in the "after update" event for date issued, but it does not give me the option to select this query..... only macros, expressions or code.

I guess I need to know what is the simplest way to do this....

thank you again.

[ibgreat]

Based on you post is sound like you are familiar with VBA or at least have some programming experience. Due to this I would recommend staying away from macros. Focusing on queries and VBA is the more effecient and cleaner way to do things. (also it makes it easier for us to help you)

Now that you have explained a bit more it sounds like you want the form to show the user the cycle time. So if I understand you correctly you want the form to show the difference between the date issued and the date closed or date issued and today. If this is the case, you probably don't need to store the cycle time data. You can use events to complete the calculation and place a the value on the form (it doesn't have to come from a table). Therefore you wouldn't need the UPDATE query as you aren't changing any actual table values.

To do this you will need to set up you expression and do minimal VBA coding. I am assuming that the form that you are using is bound to the table. (An aside, I would remove spaces from your table names, use underscores instead, it will make later coding less prone to error and problems.) You can check this by opening your form properties and seeing if anything is in the RecordSource field under the Data Tab. Okay, based on those assumptions here we go.

1. In design view for your form add a text field that will be used to show your "cycle time". Name it appropriately and all that good stuff. We'll call it txtCycleTime

2. Now go into the properties of txtCycleTime and on the Data tab, Click in the controlSource box and you will see "..." appear. Click on the "..." and the expresssion builder will open up. You can build your expression there. I believe you can create If then statement there. (I don't usually use bound forms and do most of my coding in the VBA so I am not positive). Look up the syntax of If...then in help if you need to.

3. Then within your txtDateIssued and txtDateClosed text boxes open the events and choose "AfterUpdate". Within the procedure add Me.txtCycleTime.Requery. This will tell the form that after you enter a new value in either field to recalculate the txtCycleTime.

If the form is unbound:
The procedure is similar, but you would likely use queries within your textboxes rowsource.

Once you have tried some of these things feel free to post again. Include the actual expressions and code you have used.

[Dargo]

I apologize for being unclear in my post, but I DO want the "cycle time" to be visible on the Form (and automatic/non-user-editable) AND SAVED to a number field in my table "Cycle Time". Therefore, on the form, the control source for "cycle time" is indeed "cycle time" from the table.

I hope this explains things a little better.

Thank you,

[Dargo]

Help..... anyone