Time Formats

[GendoPose]

Hi all,

I need to store the hours a job will run for on my form. There are 4 calculated fields, each based on production department. The HOURS field will then calculated the time based on what production department is selected, and then using the relevant calculated field.

However it's entirely possible that a job will run for more than 24 hours, yet access resets to dd/mm/yyy 00:00 when you go over 23:59. Is there a way to format the field to show DD:HH:MM?

The calculated fields are;

(([Quantity]/[Run Rate])/24)

Thanks guys.

 

[pr2-eugin]

Calculated fields belong in queries, not tables. Do not store calculated values in tables, collect only the Start Date/Time and End Date/Time then you can easily get the difference in a Query using a DateDiff function.

 

[GendoPose]

The calculated field isn't being stored in the table, just the result of the calculation, which is what I need to store as DD:HH:MM

EDIT: I should also mention, this is a Time Elapsed field, so there is no Start Date/Time and End Date/Time that I can work out, as I would need to work out the time taken for this anyways.

 

[pr2-eugin]

so there is no Start Date/Time and End Date/Time that I can work out, as I would need to work out the time taken for this anyways.

That's what I am saying, you need to collect the information of when the project was assigned/started and when it ends, so you can easily in matter of seconds find out the needed info.

So what are the fields involved and give some sample data to give a little bit of idea.

 

[GendoPose]

1. Quantity is entered in relevant box (Total, Machine, Bench or Inkjet)
2. Production Department is chosen (Machine, Bench, Machine and Bench, Inkjet)
3. Run Rate is entered manually (this is a value of X packs per hour)
4. There are 4 calculations, 1 for each production department. The calculation is: (([Quantity]/[Run Rate])/24) to work out the hours.
5. The HOURS field then shows the result of the calulcation from the box relating to the production department.

 

[Walshie]

Hi again!

assuming your "hours" textbox has the hours like 24.5 for 24hrs 30mins etc. you could use the following:

assuming your hours text box is called txthour in this example

=Int([txthour]/24) & " days " & Int([txthour]-(Int([txthour]/24)*24)) & " hours " & Format(([txthour]-Int([txthour]))*60,"00") & " Minutes"

Cheers
Chris

 

[GendoPose]

the textbox is formatted as Short Time, but I changed the format on the table to number, and then entered that code into the format box, but it just had a lot of ####'s when I opened form view.

 

[GendoPose]

For anyone who comes across this, I finally figured it out.
You have to create a module in vba with the following code:

Function FormatDecimalTime(DecimalTime As Single) As String
    Dim lngHours As Long
    Dim sngCalculated As Single
    sngCalculated = DecimalTime / 24#
    lngHours = Hour(sngCalculated) + _
    24 * Int(sngCalculated)
    FormatDecimalTime = Format(lngHours, "00") & ":" & _
    Format(sngCalculated, "nn:ss")
End Function

Then on my calculated field, I had to wrap the calculation as "FormatDecimalTime([CALCULATION)"
You also have to make sure the field type is set to text in your record table.

Thanks for all the help guys.