John,
I have no idea where the "FIRST" came from - it was in the pop up syntax
error message box.
I used your formula exactly (adding an = sign in front when pasting it into
the control source field). I have never done a formula like that with the
format in it – is that the problem? Should I be doing something else? Here
is what the fields look like:
CONTROL SOURCE =Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")
INPUT MASK 00:00;0;_
FORMAT Fixed, DECIMALS 2
As for using the query to do this, I don’t know SQL that well, and since I
am summing by group and then the master report total, I don’t have those
types of fields in the query, so I don’t know how to do that, and then make
them show in the report properly. I have always used regular numbers when
doing this, not time, and have never had this issue before.
Thank you for your help!
"John Spencer" wrote:
> Where did FIRST come from? As far as I know you cannot combine aggregate
> functions in one query.
>
> If you are doing this in query design view, try using EXPRESSION as the choice
> in the Total box under the expression above.
>
> Otherwise, post the SQL of your query.
>
> (Possibly unneeded instructions follow)
> Open the query
> Select View:Sql from the Menu
> Select all the text
> Copy it
> Paste it into the message
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> PolQueen wrote:
> > Thank you, John.
> >
> > When I try to use:
> > Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
> > Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")
> >
> > I get this message: Syntax error in query expression
> > 'First([Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
> > Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")])'
> >
> > Should I be doing something else?
> >
> > Thank you.
> >
> >
> >
> > "John Spencer" wrote:
> >
> >> Sum(DateDiff("n",[StartTime],[EndTime]) gives you the number of minutes
> >>
> >> Sum(DateDiff("n",[StartTime],[EndTime])\60 gives you the number of hours (that
> >> is integer division so it removes the fractional portion).
> >>
> >> Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60 gives you the number of
> >> leftover minutes.
> >>
> >> So for display purposes in hours and minutes:
> >> Sum(DateDiff("n",[StartTime],[EndTime])\60 & ":" &
> >> Format(Sum(DateDiff("n",[StartTime],[EndTime]) Mod 60,"00")
> >>
> >> 27.08 is 27 hours and 5 minutes (actually the number is probably
> >> 27.08333333333 but your 2 decimal places is rounding that off).
> >>
> >> Your problem with the first solution is you are attempting to show a duration
> >> of time using a point in time. 27 hours and 5 minutes. A point in time is
> >> never going to exceed 24 hours. Once it does, you are going to end up with a
> >> day (date) plus the time left over after you divide by 24 hours.
> >>
> >> John Spencer
> >> Access MVP 2002-2005, 2007-2010
> >> The Hilltop Institute
> >> University of Maryland Baltimore County
> >>
> >> PolQueen wrote:
> >>> I know that working with time in Access can be tricky. I have multiple
> >>> locations that I am tracking times that vehicles are covering for another
> >>> vehicle, and I really need to use time format. When I have only a few
> >>> entries, the first formula works with no problem, but with a lot of entries,
> >>> it is incorrect:
> >>>
> >>> =Sum([StartTime]-[ EndTime])
> >>> Format: Short Time
> >>> Input Mask: 00:00;0;_
> >>> (25 entries ranging from :45 minutes to 1:20 minutes = 3:05 hours, which is
> >>> wrong.) When I have a lot of entries (some are in the hundreds), the above
> >>> formula is completely wrong.
> >>>
> >>> I can get close to the correct number with the formula shown below, but it
> >>> is no longer in time format and I am questioned why something is out of
> >>> service for 27.08 hours when everything is always in five minute increments.
> >>>
> >>> =Sum(DateDiff("n",[StartTime],[EndTime])/60)
> >>> Format: Fixed
> >>> Decimal Places: 2
> >>> (The same 25 entries ranging from :45 minutes to 1:20 minutes = 27:08 hours,
> >>> is actually 27:05 hours).
> >>>
> >>> What is the proper way to calculate this so that I get the true time?
> >>>
> >>> Thank you.
> >>>
> >> .
> >>
> .
>
Similar Threads
