Sequencing Dates Accross Multiple Fields

[Halldo]

I am developing a timing tool, that calculates the time necessary to perform various task when a part is ordered. The dilemma that I have is that the activities do not have to occur in any particular order (or sometimes not at all). The Start and End time are the only ones that a guaranteed order. The timing is calculated by subtracting the event from the event immediatly preceeding it. I wrote some code, however what I wrote always took the start time as the beginning of the activity. I am trying to find some visual basic code to support the sequencing of dates across multiple manual process. I have attached an example of the Access table in Excel. Any help or suggestions would be greatly appreciated.
Thanks,
Halldo

[OBP]

Hello Halldo, I would prefer to work in Access if possible.
I have looked at your Excel sheet and I can Import it in to Access and work on it there, but I don't actually know what the calculations are that you want to Code.
I have coded quite a few "Date", "Time" or "Date/Time" oriented databases so if you can tell me what the calculation is for each field that you want a calculation done on and more importantly where you want to put the result, I will have a go at it for you.

[Halldo]

OBP,
I have attached a database with 2 tables in it. UCC_Fields are the date fields with the formula that I would like to use, it is a simple subtraction formula. At the beginning of each formula is the field name I would like to capture the results into. Example - CreateToMSC = MSCTime - minimum date prior, CreateToMSC is the field to capture the result into, MSCTime is the next date to use, and minimum date prior is the closest less then date in the series. UCC_Orders_Sample is some sample data to test with. The minimum date prior is the date that I am have difficulty identifing, if I could some how sequence the dates with a reference to the field name they came from I can get the rest. Thank you for any help you can give me.
Halldo

[OBP]

Halldo, I have created the code that sorts your various Date Fields in to Order and puts the calculated value in to the relevant Field. (Well the first one up to now).:yes
But I can't continue because I have a little Problem, the fields that your calculated values are to go in are Type Number - Double Precision.
I need to know what type of value you want to place in those fields, will it be the difference in dates in Seconds, Minutes or Days.
Your Dummy Data suggests that it would be in Minutes.

Also the first field "CreateToMSC" is not the same as the rest and is a Date/Time field.
If you could assist me with this I can complete this for you. :help

[Halldo]

OBP
I am looking for this number to be in either Day Parts or in Minutes either one will do. What I have done in the past is just allowed Access to update the value based on the formula DateTime1 - Datetime2 = Answer.
Example: 12/1/2006 10:10 - 11/30/2006 17:18 = 0.702777777776646. Or approximately 70% of a 24 hour day.

the CreatetoMSC field should also be a number (double precision field).

Hope this makes sense, if not let me know.

Thank you for your help,
Halldo

[OBP]

Halldo, this is a first go at this, what happens when you click the Command Button on the form is this.
In the Visual Basic the Dates along with the field names for that record are placed in a table called "Sort Table".
There is a query based on that table which sorts the dates in ascending order.
The Visual Basic then opens that query and steps through the records looking for your Field Names, when it finds a field name it carries out the calculation as per your requirements. It places the result in the relevant Field.
I am not sure if I have captured all of the field calculations that you need, if you can't figure out how to do any I have missed let me know.

I hope it has survived converting from Access 97 to Access 2000 and back again. :dunno

[Halldo]

I will have a look at it. thank you for your help.
halldo

[Halldo]

OBP,
The logic you provide works very well. I have modified it to move through records in a recordset, but your logic remains intact. Thanks for all your help.
Halldo

[OBP]

Halldo, glad to have hleped, any chance of letting me have a copy of the Recordset version for my files?

[Halldo]

OBP,
I am happy to share it, however I wrote it in the active tool which I can not share for two reasons- First and foremost it contains Company confidential information and Second the shear size of the tool. However, I have loaded the test tool that you developed with the recordset version for your records. Sorry the code itself is in the module called timing tools and the public function is UCC_TimeCalc. Hope it helps, look it over if you should need any clarification let me know.
Cheers :beerchug: ,
Doug

[OBP]

Thanks. :thumb