Sum of multiple TIME entries of a table

[DeanEdwards2012]

Hi all!

I'm in engineering, and im in the middle of developing a database to log DOWNTIME of various machines in the factory. Basically myself and others will use it to log breakdown details and the amount of time (in HH:MM) the machine was off.

I have a form which will list details of a certain machine in a list box which i can populate using a query.

The list box will display 3 columns: Date of Breakdown, Machine Name and Downtime

I then have a text box below this that i would like to display the TOTAL amount of downtime for that machine (The listbox may have different information in it depending on the query criteria, e.g. It might display a certain month for a certain machine).

THE ISSUE i am having is its proving to be difficult to get the correct sum of the accumulative time to display correctly in the text box.

Is there a way i can use a query to calculate the total time of a column and return a value which is formatted as time (like this hhhh:mm or dd:hh:mm) or preferably use some VBA code on the form itself to total up the DOWNTIME column of the list box and display it with the correct format in the text

I have gotten close, but as the total amount of time exceeded 24 hours, it went past 00:00. I'm assuming it treated it as a clock and went into the next day? This is NOT what i am after, i need the total amount of days, hours spent on the machine and not a time of the day.

I hope someone can help as i have been struggling with this for a while :banghead:

Much obliged

Dean

 

[MLUCKHAM]

Convert your downtime into minutes. What is the date type of this field? If it is a string you will need to use cint, left, instr, right, len and instr. i can give you the formulae if you are stuck on this one.

Once you have the minutes you can sum this in your query. I would then write a vba public function which would take the total minutes in and return days, hours, minutes.

Formula for function would be:-

Days = TotalMins / 1440
RemMins = TotalMins mod 1440
Hours = RemMins / 60
Mins = RemMins mod 60

Return Days, Hours, Mins

 

[DeanEdwards2012]

Thanks, how do i convert the downtime values into minutes? do i need to do this in the query? if so, do i need to create an extra colum and convert each downtime record then sum these?

the datatype for the downtime field is 'short time'

im assuming i use the vba functions on the open form event (Or if the listbox/query is changed) and divide the query value on the form and not in the query?

 

[MLUCKHAM]

So many questions!

Short time makes it easier. So in your query you add a column which has something like (cint(format([Downtime],"hh"))*60) + cint(format([Downtime],"n")). Call it TotalMins.

In your form you can Sum this field into a field if you like ( as it is a integer). However, I would put a field on your form and put =GetDaysHoursMins(Sum(TotalMins))

Now, the GetDaysHoursMins is your public vba function which I outlined above...

 

[DeanEdwards2012]

Actually getting somewhere! one more thing which may sound like a simple question, but how do i call the result of the query (The (cint(format([Downtime],"hh"))*60) + cint(format([Downtime],"n")) one) and integrate it into the public function on the form?

so that i can then convert the value into dd:hh:mm?

the query you gave me works, i have summed this column and i have a total value as an integer, i have created the function in my module (I think) and can call this function in the on open event of my form, how do i get the value returned from the query into the function so that it is days hours mine?

much appreciated

Dean

 

[MLUCKHAM]

Not sure I would call this function on the open form.

I expected you would have your query connected to a form. So the form displays each record and then in the footer you could Sum your new field.

What you can do is add an unbound text box in the footer and then call the function like =GetDaysHoursMins(Sum(TotalMins)).

You would then have displayed the total days, hours and minutes.

The function needs to be public for it to be visible from the form.

 

[MLUCKHAM]

If you post your database I can knock up an example if you like...

 

[DeanEdwards2012]

Thanks, i have attached the file,

was unable to send the complete file as it is too large, but i have put the ones used on the form on a table

also, there are 3 listboxes on the form which relate to other tables (So downtime can be calculated for other reasons) which is why i couldnt set the control source for the form to a single table/query

thanks for your help!

Dean

 

[DeanEdwards2012]

im still fairly new at all of this, and learning most of what im doing through forums, so this is a massive help as its something i've been struggling with for a while

 

[MLUCKHAM]

I have amended your query1 to show the result.

If you put your public function in a module you can access it from queries aswell as forms.

Run query 1 and take a look...

The code is in GlobalCode.

 

[DeanEdwards2012]

Fantastic! Your a life saver! i would have been scratching my head for days, so thank you very much!

I put TotalTimeDHM: GetDaysHoursMins(Sum((CInt(Format([Downtime(Hrs)],"hh"))*60)+CInt(Format([Downtime(hrs)],"n")))) in the control source of my text box and it worked perfectly so thanks very much