Problem with sum in my form

[Jackrobyn]

I have a continous form thats control source is a qry, the form records lost
time like so.....

Name Start time End time Lost Hours
John 12:05 13:10 01:05
Keith 14:20 15:00 00:40
Fred 14:55 15:05 00:10

The Lost Hours field is calculated on my QRY with the following field name
and formula

Expr1: (DateDiff("n",[Time from],[Time to]))\60 & Format((DateDiff("n",[Time
from],[Time to])) Mod 60,"\:00")

This works great but I want my form to display a single Total Lost Hours
field in the form footer that adds up all the lost hours diplayed and shows
the TOTAL. I have tried everything but the text box i use just diplays #error
all the time!! Ive tried everything almost. Should i be using my qry to give
me this or just the text box? Would someone mind letting me e-mail a copy of
that part of my database to them for this fix as this is so important to what
im trying to achieve, its driving me mad!

 

[John W. Vinson]

I have a continous form thats control source is a qry, the form records lost
time like so.....

Name Start time End time Lost Hours
John 12:05 13:10 01:05
Keith 14:20 15:00 00:40
Fred 14:55 15:05 00:10

The Lost Hours field is calculated on my QRY with the following field name
and formula

Expr1: (DateDiff("n",[Time from],[Time to]))\60 & Format((DateDiff("n",[Time
from],[Time to])) Mod 60,"\:00")

This works great but I want my form to display a single Total Lost Hours
field in the form footer that adds up all the lost hours diplayed and shows
the TOTAL. I have tried everything but the text box i use just diplays #error
all the time!! Ive tried everything almost. Should i be using my qry to give
me this or just the text box? Would someone mind letting me e-mail a copy of
that part of my database to them for this fix as this is so important to what
im trying to achieve, its driving me mad!

That's because your Expr1 is a formatted text value - which cannot be summed.

Put TWO expressions in the query:

LostMinutes: DateDiff("n", [Time From], [Time To])

as well as your current formatted expression. In the form Footer put the
formatting stuff to cast the 317 minutes lost into 5:17 format.

 

[Damon Heron]

Here is one solution:
in your query add another field: CInt(DateDiff("n",[Time from],[Time to]))
AS Expr2
this will give you the total minutes for each. Save the query
Create a second query with the first query as its source, using sum. So,
you would have "sumofExpr2" as the only field, with the total minutes of
all.
Now, put your continuous form as a subform on a main form, where the
recordsource is the 2nd query. Add a text box, with the control source as:
=Int(([SumOfExpr2])/60) & ":" & (([sumofexpr2]) Mod 60)
As you add records to the subform, you will want to requery the parent.

This really seems like a kludge, but it works. Perhaps someone else will
have a more elegant solution.

Damon

 

[Jackrobyn]

WOW! thats a lot to take in! ill give it ago but that sound over my head.
Can't i e-mail it to one of you?