Auto sum of Date/Time rows.

T

Tim

I'm having difficulty using the sum funtion to add up the date/time fields in
access. When I cut and past into Exel it gives me an error 'Negative dates
or times are displayed as ####'. I can add up small amounts of time and the
function works just fine, so I'm wondering if there might be a row limitation
when adding or something? If anyone can help it would be much appreciated.
Thanks.
 
J

Jeff Boyce

Tim

A couple of the things you "said" make me wonder about the underlying data
....

If you are trying to add something up in a "row", that suggests that there
are multiple instances of the same kind of thing in multiple columns across
one row. This is exactly how you would do this ... if you only had a
spreadsheet to use.

If you have "repeating columns" (the same kind of information but in
adjacent columns), your data needs further normalization if you want to use
the features and functions that Access provides. Specifically, the Sum()
feature in Access adds DOWN, not across. This is because, in a
well-normalized table, the values of interest would be in successive rows,
not columns.

If your column (or columns - see previous comments) is a date/time data
type, you CANNOT add them up. Or rather, you can add them up, but it won't
mean anything. Access Date/Time fields record "point-in-time" data, not
duration.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP
 
J

John Vinson

I'm having difficulty using the sum funtion to add up the date/time fields in
access. When I cut and past into Exel it gives me an error 'Negative dates
or times are displayed as ####'. I can add up small amounts of time and the
function works just fine, so I'm wondering if there might be a row limitation
when adding or something? If anyone can help it would be much appreciated.
Thanks.
Click to expand...

What is the sum of March 4, 2005 and March 8, 2004?

Dates are actually stored as numbers - counts of days and fractions of
a day since midnight, December 30, 1899 - but it makes absolutely no
sense to sum them. Could you explain what you are trying to
accomplish? Normally the DateAdd() function is most useful here.

If you're storing *DURATIONS* rather than points in time, it's best to
store them - not in a Date/Time field - but in a Long Integer or
Double Float in the finest time gradation you need. For instance, if
you're counting minutes, store 2 hours 30 minutes as 150; use an
expression like

[Duration] \ 60 & Format([Duration] MOD 60, "\:00")

to display the result as 2:30.

John W. Vinson[MVP]
 
Top