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

 

[dhookom]

Gaime,
If you expect assistance, you must be more helpful. "it didn't work" doesn't provide the slightest indication of what might be wrong.

Are any of your "time" fields blank? Could you please create a query that selects the two time fields for about 10 records and paste the results into a reply.

Also, what is the SQL view of what you tried and the exact result that you suggest "didn't work".

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]

 

[PHV]

0.00208333333333333 and 0.00277777777777777
So 3 and 4 minutes ...
Simply replace Sum(...) As DownTime by Format(Sum(...), "hh:nn") As DownTime

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

 

[Gaime]

dhookom,


Begin Einde[/color brown]
22:00 23:55
21:24 22:00
17:28 17:48
09:00 09:19
14:00 14:12
04:24 04:34
06:00 06:10
18:27 18:36
04:15 04:23
07:10 07:18
00:37 00:45
[/color blue]

this is an example of what is in the database.
There are no time fields blank because a record can't be given in without the timefields.

I tried this code:


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

The error message said:

Datatype mismatch in criteria expression.
[/color red]

hope this clarifies a lot.

gaime

 

[dhookom]

Gaime,
Open a debug window (press Ctrl+G) and enter each of these lines and press enter. Watch for wrapping since each of these three lines should be a single line.

? TimeDur("0600","0610")

? dlookup("txtEindeStoring","tblStoring")

? Currentdb.TableDefs("tblStoring").Fields("txtEindeStoring").Type

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'm sorry but I'm really a newbie,

what should I do exactly?

I pasted the lines you gave me in the boxes I also put TimeDur.
I tried to compile (because I didn't really know what I was supposed to do) and it gave the error:

compile error:
Invalid outside procedure[/color red]

can you help me some more?
thx
gaime

 

[PHV]

Have you tried this ?
SELECT S.txtDatum AS Datum, M.Cell AS Cel, S.txtFCode AS Foutkode,
Format(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"))), "hh:nn") 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;

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,
How far did you get with my suggestion? Did you "Open a debug window (press Ctrl+G)"? From the database window, press Ctrl+G to open the debug window. Enter the lines as per my earlier posting in the debug window.

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]