[AccessD] Summing Time is missing minutes SOLVED

Don

That was certainly it!

sttlTimeDiff:
Format(Sum(IIf([EndTime]>[StartTime],[EndTime]-[StartTime],(DateAdd("d",1,[E
ndTime]-[StartTime]))),"h:nn")
ttlTimeDiff:
Format(Sum(IIf([EndTime]>[StartTime],[EndTime]-[StartTime],(DateAdd("d",1,[E
ndTime]-[StartTime]))),"h:nn")

The only difference with this is that the subtotals and totals now print as
hours and minutes (5:30) instead of hours (5.5). That works fine for this
application.

Thanks a HUGE BUNCH!

As always, this list is great!!!

Bob Gajewski


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mcgillivray,
Donald [ITS]
Sent: Monday, July 12, 2004 16:29
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Summing Time is missing minutes


Bob,

When you wrap a value in a Format function, the result is returned as a
string (as you obviously know.)  When you convert that string using the
Val function, the conversion halts at the first non-numeric character in
the string, in your case the colon between the hours and minutes.
You'll have to convert the string to a time value again before summing
and reformatting.

HTH,

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Gajewski
Sent: Monday, July 12, 2004 12:47 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Summing Time is missing minutes


Dear Group:

This is a similar issue to the recent one about summing time.

I have two actual fields and one calculated field in each detail record:

	StartTime
	EndTime
	txtTimeDiff (calculated)

On the report, I print the StartTime, EndTime, and txtTimeDiff - all
using
Short Time format.

I group and subtotal the data at the deepest level. The detail records
show
the true time difference (such as 02:30 for two and one-half hours
difference). PROBLEM: My subtotals and totals for the txtTimeDiff are
only
adding the integers (hours); not the minutes (In the above example, it
is
only adding two hours). All my fields are set to one decimal place. Any
suggestions where I have gone awry? Below is the code and a sample
output:

TIA
Bob Gajewski


REPORT CODE
====================================================
rptMembers
  FullName Header
    IncidentClassMajorName Header
      IncidentClassMinorName Header
        Detail
          [PageNumber] [StartTime] [EndTime] [txtTimeDiff]

txtTimeDiff:
IIf([EndTime]>[StartTime],Format([EndTime]-[StartTime],"Short
Time"),Format(DateAdd("d",1,[EndTime]-[StartTime]),"Short Time"))

      IncidentClassMinorName Footer
          [sttlCountIncidentID] [sttlTimeDiff]

sttlCountIncidentID: Count([IncidentID])
sttlTimeDiff: Sum(Val([txtTimeDiff])) Format 0.0

    IncidentClassMajorName Footer
  FullName Footer
    [ttlCountIncidentID]

ttlCountIncidentID: Count([IncidentID])
ttlTimeDiff: Sum(Val([txtTimeDiff])) Format 0.0


REPORT SAMPLE OUTPUT
====================================================
Jones, John
  Drill
    Fire Drill
      19:00  20:00  01:00
      19:00  20:30  01:30
        STTL   2      2.0 hrs       'should be 2.5
    EMS Drill
      23:00  02:00  03:00
      19:30  21:00  01:30
      10:00  15:30  05:30
        STTL   3      8.0 hrs		'should be 9.0
         TTL   5     10.0 hrs		'should be 11.5




--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com