counting time

[Jean-Paul]

Hi,
I have a table with 3 "time" fields:
1 starttime
1 stoptime
1 "worked time"

I created a querry to calculate the "worked time" (simple:
stoptime-starttime)

Finally I have to sum all "worked time"s together
I do this with following SQL:

sql = "SELECT Sum(Uren.gewerkt) AS SomVanUur FROM Uren WHERE
Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat, "DD/MM/YYYY")
& "# And Uren.Datum < #" & Format(Forms![Uur_overzicht]!Stop_dat,
"DD/MM/YYYY") & "# ;"
Set kost = db.OpenRecordset(sql)

Then I enter the value in an entryfield:

Me!rek = Format(kost!somvanUur, "hh:mm")

the somvanUur is 1,229166
Me!rek shows 05:30 while it should be 28:30

What am I doing wrong?

Thanks
JP

 

[Marshall Barton]

I have a table with 3 "time" fields:
1 starttime
1 stoptime
1 "worked time"

I created a querry to calculate the "worked time" (simple:
stoptime-starttime)

Finally I have to sum all "worked time"s together
I do this with following SQL:

sql = "SELECT Sum(Uren.gewerkt) AS SomVanUur FROM Uren WHERE
Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat, "DD/MM/YYYY")
& "# And Uren.Datum < #" & Format(Forms![Uur_overzicht]!Stop_dat,
"DD/MM/YYYY") & "# ;"
Set kost = db.OpenRecordset(sql)

Then I enter the value in an entryfield:

Me!rek = Format(kost!somvanUur, "hh:mm")

the somvanUur is 1,229166
Me!rek shows 05:30 while it should be 28:30

What am I doing wrong?

You're trying to format a duration as a (date/) time. That
value is actually the date 31 Dec 1899 05:30

You should look into using the builtin date/time function
subh as DateDiff to do the date/time difference in a a basic
unit such as minutes.

Until you figure that out, you can munge your current "time"
value by using this kinf of hokey expression:

Int(somvanUur * 24) & Format(somvanUur, ":nn")

 

[Jean-Paul]

I have a table with 3 "time" fields:
1 starttime
1 stoptime
1 "worked time"

I created a querry to calculate the "worked time" (simple:
stoptime-starttime)

Finally I have to sum all "worked time"s together
I do this with following SQL:

sql = "SELECT Sum(Uren.gewerkt) AS SomVanUur FROM Uren WHERE
Uren.Datum > #" & Format(Forms![Uur_overzicht]!Start_dat, "DD/MM/YYYY")
& "# And Uren.Datum < #" & Format(Forms![Uur_overzicht]!Stop_dat,
"DD/MM/YYYY") & "# ;"
Set kost = db.OpenRecordset(sql)

Then I enter the value in an entryfield:

Me!rek = Format(kost!somvanUur, "hh:mm")

the somvanUur is 1,229166
Me!rek shows 05:30 while it should be 28:30

What am I doing wrong?

You're trying to format a duration as a (date/) time. That
value is actually the date 31 Dec 1899 05:30

You should look into using the builtin date/time function
subh as DateDiff to do the date/time difference in a a basic
unit such as minutes.

Until you figure that out, you can munge your current "time"
value by using this kinf of hokey expression:

Int(somvanUur * 24) & Format(somvanUur, ":nn")

OK... problem solved!
Thanks