Calculating Time, Please Help! 1

[jen1701]

I am trying to calculate the time based on the condition in a form. The user will select the bake condition at 125C or 40C, thickness (<1.4mm, <2.0mm, <4.0mm) and level(3,4,5,). So when the part is in the over at 125C, thickness <1.4mm and level 3, I need to calculate the ScheduledPartOutTime which is PartInTime + Baked hours (or days)(eg. 1/12/2004 3:15:12 + 48hours or 1/12/2004 3:15:12 + 52days). The PartInTime is auto load when the user starts to select the conditions. I am trying to put the logic in the AfterUpdate of Level field. So when the user select the level, it will populate the ShceduledPartOutTime based on the calculation. But I am not sure how to calculate the time. Please Help! Thank you so much in advance.

Jen

 

[SteveR77]

Jen,

If the Baked hours will always be in days then you can use the PartInTime + DateAdd(&quot;d&quot;,<value for days>,[PartInTime])


HTH,


Steve

 

[jen1701]

Hi Steve,

Thank you for your quick reply. The baked time is not always in days. If the baked @125c, the baked time will be 7, 9,10,14,31,37 hours. I am not sure how to calculate the hours. Do I need to DatePart PartIntime and add the required hours and calculate the result?

Jen

 

[SteveR77]

Jen,

No problem, if hours are going to be the base for doing the calculation then the DateAdd will look like what is below -

PartInTime + DateAdd(&quot;h&quot;,<value for calculation as hours>,[PartInTime])

So a day would be expressed as 24 hours instead of 1.

Steve

 

[jen1701]

Steve,

Thank you so much for the help. I tried your suggestion. Should it just be DateAdd(&quot;h&quot;,<value for calculation as hours>,[PartInTime]) or DateAdd(&quot;d&quot;,<value for calculation as hours>,[PartInTime]). eg. DateAdd(&quot;h&quot;,31,[1/24/2004 1:03:21]) became 1/25/2004 8:03:21 PM , DateAdd(&quot;d&quot;,<value for calculation as hours>,[PartInTime]). eg. DateAdd(&quot;d&quot;,30,[1/24/2004 1:03:21]) became 2/23/2004 1:03:21 PM. Is it right?

jen

 

[SteveR77]

Jen,

In the DateAdd d is for days and h is for hours.

If you want to express hours as fractions of days then use the function with the d option, but I would use hours as the base so 3 days would be 72.


Steve

 

[jen1701]

Steve,

Thank you so much for your help. I gave you a star for that. Now I have another question. How to add the following date and time together as Total datetime.

1/2/2004 1:03:21 AM
1/25/2004 12:08:21 PM
12/27/2003 2:19:41 AM
3/24/2003 1:03:21 PM
1/25/2004 12:08:21 PM
1/27/2004 2:19:41 PM

Thanks,

Jen

 

[SteveR77]

What are you needing as a result for total time?

Hours, days, minutes?

Steve

 

[jen1701]

I have a set of date and time and want to calculate the total date and time. This is in a report. I grouped on Botton/Top. eg:
Bottom side DateIn:
1/14/2004 8:25:28am
1/15/2004 7:03:55am
1/15/2004 11:43:20am
In the group footer, I want to know the summary of total Bottom side DateTime in days,hours,mins,secs

Thanks,

Jen

 

[SteveR77]

Jen,

Is something like what is below what you are after?

If so can we treat the first record as the start time and the second as the end and continue that logic forward of the second and third, etc.

Botton/Top. eg:
Bottom side DateIn:
1/14/2004 8:25:28am
1/15/2004 7:03:55am <--- 22.6408333334257 hours
1/15/2004 11:43:20am <--- 4.65694444446127 hours

Total of 27.297777777887 hours

 

[jen1701]

Steve,

Thank you so much for putting thoughts on my problem. I am very preciated.

Each DateTime is for each board. I actually do need to calculate the duration between each board. eg. 1/15/2004 7:03:55am - 1/14/2004 8:25:28am. I also need to know the duration for the bottom group. eg. 1/15/2004 11:43:20am -
1/14/2004 8:25:28am.
By the way, how did you get 22.6408333334257 hours and 4.65694444446127 hours? Did you convert the datetime to hours? How to convert it back to DaysMinsSecs format

Thanks,

Jen

 

[SteveR77]

Yes, I played with the date calculations a bit.

Basically, it worked this way.

Abs(Date Row1 - DateRow2) * 24

Abs(DateRow2 - DateRow3) * 24


The reason I was wondering if what I though was right is related to possibly being able to do something like the running sum on a query or working with the previous record's value with some VBA code.

I know that what you want to do is possible it, it is just a matter of understand the logic.

I have no doubt some of the other guru's on the site will have some ideas on this as well.

 

[jen1701]

Thank you so much for all the help. I gave you another STAR. I will try your suggestion and probably will come back to you again.

Jen

 

[SteveR77]

No problem Jen. Glad I could help.

 

[jen1701]

Hi Steve,

How did you store the DateRow1, DateRow2, DateRow3... in a query? Is there a function which I can use?

Thanks,

Jen

 

[SteveR77]

Jen,

If you search the Access forums. I know there have been prior post related to value from the previous record.

The mechanics are straight forward.

Get a record

Capture the date value you need in a variable

Get the next record

Capture its date value in a varible

Subtract the two date values

Capture the absolute value of the subtraction

Add next calculated value to previous

Go to next record and repeat the process to end of the recordset.

I haven't written any code to do this as of yet Jen.

Steve

 

[jen1701]

Thanks Steve.

Jen

 

[SteveR77]

No problem at all Jen.