The sum of times, given in by a mask

[Gaime]

Hi guys,

I need some expert help about a problem I'm having with summing up times.

The times are given in by a mask "__:__" [/color green]. So the times range from "00:00" to "23:59".

With some help I made a table who tells the downtimes of a cell (it's for calculating downtimes of a machine).
So it's endtime - starttime.

The tables which hold the begintimes and endtimes are 'txtBeginStoring' and 'txtEindeStoring'.

The downtimes are calculated by using the following string:

IIf([txtBeginStoring]<=[txtEindeStoring],Format(CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time"),Format(1+CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time")) AS Downtime,
[/color green]

Now I need a total of all these calculated downtimes (thus a total of the downtime of a cell for a given day)

Can somebody help me with that?

thx

gaime

 

[PHV]

Why aren't begintimes and endtimes stored as DateTime values ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaime]

Because I'm working with somebody else his database (at work) and there are many records, and I'm not so keen on making changes which may damage the database. So I would like to find a solution for the database as it is (with its malfunctions). I already asked myself the same question, why he made it like it is (with the mask), and not with datevalues?

It would make life a lot easier...

thx
gaime

 

[dhookom]

You should be able to convert your time strings to datetime values with TimeValue([txtEindeStoring]).

I would not apply a format to your result since that converts a datetime back to a string which is never a good idea unless you are passing the value out of access. All formatting should be done with the format property of controls on forms and reports.
[green]
IIf([txtBeginStoring]<=[txtEindeStoring], TimeValue([txtEindeStoring]) - TimeValue([txtBeginStoring]), TimeValue([txtBeginStoring]) - TimeValue([txtEindeStoring]) AS Downtime,[green]

If this doesn't work, come back with your symptoms.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Gaime]

I tried your option but I had the following error:


Data type mismatch in criteria expression. [/color red]

Another question I asked myself: it is necessary that downtime who include midnight also are counted correctly. with the expression I have already this is done (I don't think your expression includes this exception: for example 23:55 till 00:15 is 00:20 minutes downtimes.

Now I'm looking for the sum of all calculated downtimes. Maybe it would be more clarified if I show the whole code who works till now:

The code is:


SELECT

tblStoring.txtDatum AS Datum,
tblMachine.Cell AS Cel,
tblStoring.txtFCode AS Foutkode,
tblStoring.txtBeginStoring AS [Begin],
tblStoring.txtEindeStoring AS Einde,

IIf([txtBeginStoring]<=[txtEindeStoring],Format(CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time"),Format(1+CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time")) AS Downtime,

tblStoring.txtVerschijnsel AS Verschijnsel,
tblStoring.txtPlant AS Plant, tblStoring.txtPloeg AS Shift,
tblMachine.IDMachine AS tblMachine_IDMachine, tblStoring.IDMachine AS tblStoring_IDMachine

FROM tblMachine INNER JOIN tblStoring ON tblMachine.IDMachine = tblStoring.IDMachine

WHERE (((tblStoring.txtDatum)="4482") AND ((tblMachine.Cell)="305"))

ORDER BY IIf([txtBeginStoring]<=[txtEindeStoring],Format(CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time"),Format(1+CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time")) DESC;

[/color green]

so how do I get the sum now?
anybody any idea?

thx
gaime

 

[Gaime]

I tried this:


Format(([Downtime] & ":00"),"Short Time") As TotalDowntime
[/color green]

so I figured I now have times

If I try to sum them like this:


SUM(Format(([Downtime] & ":00"),"Short Time")) As TotalDowntime
[/color green]

It doesn't work. I get the following error:


You tried to execute a query that does not include the specified expression 'Datum' as part of an aggregate function.
[/color red]

(Maybe some help: Datum[/color blue] means Date [/color blue] in Dutch)

Does anybody know what I'm doing wrong?

thx
gaime

 

[PHV]

As you use an aggregate function (Sum) you have to use an aggregate query, i.e. a GROUP BY clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Gaime]

I tried this code:



SELECT

tblStoring.txtDatum AS Datum,
tblMachine.Cell AS Cel,
tblStoring.txtFCode AS Foutkode,
tblStoring.txtBeginStoring AS [Begin],
tblStoring.txtEindeStoring AS Einde,

IIf([txtBeginStoring]<=[txtEindeStoring],Format(CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time"),Format(1+CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time")) AS Downtime,


Sum(Format(([Downtime] & ":00"),"Short Time")) AS Total,


tblStoring.txtVerschijnsel AS Verschijnsel,
tblStoring.txtPlant AS Plant,
tblStoring.txtPloeg AS Shift,
tblMachine.IDMachine AS tblMachine_IDMachine,

tblStoring.IDMachine AS tblStoring_IDMachine

FROM tblMachine INNER JOIN tblStoring ON tblMachine.IDMachine = tblStoring.IDMachine

Group by txtDatum

HAVING (((tblStoring.txtDatum)="4482") AND ((tblMachine.Cell)="305"))



ORDER BY IIf([txtBeginStoring]<=[txtEindeStoring],Format(CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time"),Format(1+CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time")) DESC;


[/color green]

Then I get the following error:


You tried to execute a query that does not include the specified expression 'Cel' as part of an aggregate function.
[/color red]

Any suggestions?

thx
gaime

 

[Gaime]

I tried this code:


SELECT

tblStoring.txtDatum AS Datum,
tblMachine.Cell AS Cel,
tblStoring.txtFCode AS Foutkode,
tblStoring.txtBeginStoring AS [Begin],
tblStoring.txtEindeStoring AS Einde,

IIf([txtBeginStoring]<=[txtEindeStoring],Format(CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time"),Format(1+CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time")) AS Downtime,


Sum(Format(([Downtime] & ":00"),"Short Time")) AS Total,


tblStoring.txtVerschijnsel AS Verschijnsel,
tblStoring.txtPlant AS Plant,
tblStoring.txtPloeg AS Shift,
tblMachine.IDMachine AS tblMachine_IDMachine,

tblStoring.IDMachine AS tblStoring_IDMachine

FROM tblMachine INNER JOIN tblStoring ON tblMachine.IDMachine = tblStoring.IDMachine

Group by txtDatum

HAVING (((tblStoring.txtDatum)="4482") AND ((tblMachine.Cell)="305"))



ORDER BY IIf([txtBeginStoring]<=[txtEindeStoring],Format(CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time"),Format(1+CDate(Left([txtEindeStoring],2) & ":" & Right([txtEindeStoring],2) & ":00")-CDate(Left([txtBeginStoring],2) & ":" & Right([txtBeginStoring],2) & ":00"),"Short Time")) DESC;

[/color green]

And got the following error:


You tried to execute a query that does not include the specified expression 'Cel' as part of an aggregate function.
[/color red]

Any suggestions?

thx
gaime

 

[PHV]

You want the total downtime for wich entities ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Gaime,
Your error message would be the first of many. Once you add Cel to your group by, you would need to fix the next field and then the next and then the next. If you have a Group By, then every field in the select must either be an aggregate such as Sum(...) or Max(...) OR be included in the Group By field list.

Also, I never use a calculated column alias in another expression.

Does your actual [txtBeginStoring] and [txtEindeStoring] values contain ":" or are they just values like "0344"?

Input masks on tables really byte. I never use them.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Gaime]

Your right dhookom,

if I added 'Datum', I got 'Cel', and so on ...
The values look like "xx:xx" but is guess they are stored like "xxxx".

Maybey if I started a new query leaving all tables out who are nog realy necessary (except txtDatum), and then:


SUM ( ....)
Group By txtDatum
[/color green]

would I work? What do you guys think? Or is there an other possibility that might be better?

thx
gaime

 

[PHV]

You haven't answered my question.

You want the total downtime for wich entities ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

First, I would simplify this by creating a function that accepts your current field values and returns the duration in minutes. This takes the calculation out of the query and puts it where it can be used by multiple queries etc.

Also, don't apply any format in your query. Just use
Downtime:TimeDur([txtBeginStoring],[txtEindeStoring])

Add this function to a new, blank module and save the module with the name "modTimeCalcs".

Function TimeDur(strStart As String, strEnd As String) As Double
    'return the minutes of time based on
    '  two strings with a format like 0845 and 1415
    '  where the first two characters are hours and
    '  the last two characters are minutes
    'duration is correctly calculated across midnight
    'Null values for either start or end will error
    
    Dim datStart As Date
    Dim datEnd As Date
    Dim intMinutesPerDay As Integer
    intMinutesPerDay = 1440
    datStart = TimeValue(Left(strStart, 2) & _
            ":" & Right(strStart, 2))
    datEnd = TimeValue(Left(strEnd, 2) & ":" & _
            Right(strEnd, 2))
    If datStart > datEnd Then
        TimeDur = (1 - datStart + datEnd) * _
            intMinutesPerDay
     Else
        TimeDur = (datEnd - datStart) * _
            intMinutesPerDay
    End If
End Function

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Gaime]

Sorry PHV,

I want the downtimes to be calculated by Date[/color green].

thx
gaime

 

[Gaime]

dhookom,

I tried your option and got result:
the number of minutes is counted, but differ from each other.

some minutes are shown like this 1, 5 , 135[/color green] but there are also times who are shown like:

20.000000, 5.0000000, 2.0000000, ...[/color green]


Is this a problem?

How can i now get the total downtime of a day?
Can i use Sum (...) and Group by [/color green]?

Because I tried it and got blablabla 'Cel' blablabla [/color green] overnew ...
so I guess this is not the way to go?

thx
gaime

 

[PHV]

You may try this:
SELECT S.txtDatum AS Datum, M.Cell AS Cel, S.txtFCode AS Foutkode
, Sum(IIf(S.txtBeginStoring<=S.txtEindeStoring, CDate(Left(S.txtEindeStoring,2) & ":" & Right(S.txtEindeStoring,2) & ":00")-CDate(Left(S.txtBeginStoring,2) & ":" & Right(S.txtBeginStoring,2) & ":00"), 1+CDate(Left(S.txtEindeStoring,2) & ":" & Right(S.txtEindeStoring,2) & ":00")-CDate(Left(S.txtBeginStoring,2) & ":" & Right(S.txtBeginStoring,2) & ":00")) AS Downtime
, S.txtVerschijnsel AS Verschijnsel, S.txtPlant AS Plant, S.txtPloeg AS Shift
, M.IDMachine AS tblMachine_IDMachine, S.IDMachine AS tblStoring_IDMachine
FROM tblMachine M INNER JOIN tblStoring S ON M.IDMachine = S.IDMachine
WHERE S.txtDatum="4482" AND M.Cell="305"
GROUP BY S.txtDatum, M.Cell, S.txtFCode, M.IDMachine, S.IDMachine

You may have to get rid of the txtFCode field, I don't know.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Using my function, to get the sum of downtime by date, use:

SELECT tblStoring.txtDatum AS Datum,
Sum(TimeDur([txtBeginStoring],[txtEindeStoring])) as Downtime
FROM tblStoring
GROUP BY txtDatum;

If you need to add other tables and fields, just add them to the SELECT field list and the GROUP BY field list. Any field included in a Sum() or Avg() doesn't need to be included in the Group By.


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Gaime]

Dhookom,

I tried your option but it didn't work.
Is there something I might change so Id would work?

thx
gaime

 

[Gaime]

PHV,

I tried your option with following text:

SELECT S.txtDatum AS Datum,
M.Cell AS Cel,
S.txtFCode AS Foutkode,

Sum(IIf(S.txtBeginStoring<=S.txtEindeStoring, CDate(Left(S.txtEindeStoring,2) & ":" & Right(S.txtEindeStoring,2) & ":00")-CDate(Left(S.txtBeginStoring,2) & ":" & Right(S.txtBeginStoring,2) & ":00"), 1+CDate(Left(S.txtEindeStoring,2) & ":" & Right(S.txtEindeStoring,2) & ":00")-CDate(Left(S.txtBeginStoring,2) & ":" & Right(S.txtBeginStoring,2) & ":00"))) AS Downtime,

S.txtVerschijnsel AS Verschijnsel,
S.txtPlant AS Plant,
S.txtPloeg AS Shift,
M.IDMachine AS tblMachine_IDMachine,
S.IDMachine AS tblStoring_IDMachine


FROM tblMachine M INNER JOIN tblStoring S ON M.IDMachine = S.IDMachine

WHERE S.txtDatum="4482" AND M.Cell="305"

GROUP BY S.txtDatum, M.Cell, S.txtFCode, M.IDMachine, S.IDMachine, S.txtVerschijnsel, S.txtPlant, S.txtPloeg;

[/color green]

I get in the table downtime figures like
0.00208333333333333 and 0.00277777777777777 (and so on)
[/color green]

I'm looking for one number so this can.t be correct. What am I doing wrong. I'm starting to loose hope.

thx
gaime