Problem with sum in my form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jackrobyn - 11 May 2008 19:12 GMT

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!

Reply to this Message

John W. Vinson - 11 May 2008 22:05 GMT

>I have a continous form thats control source is a qry, the form records lost
>time like so.....
[quoted text clipped - 17 lines]
>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.

Signature

John W. Vinson [MVP]

Reply to this Message

Damon Heron - 11 May 2008 22:10 GMT

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

>I have a continous form thats control source is a qry, the form records
>lost
[quoted text clipped - 27 lines]
> what
> im trying to achieve, its driving me mad!

Reply to this Message

Jackrobyn - 11 May 2008 23:52 GMT

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?

Reply to this Message