Re: Sum not recoginzing a negative number

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 12/06/04 

Date: Mon, 6 Dec 2004 22:02:01 +0800

You cannot use normal floating point math with date/time values.

Open the Immediate window (Ctrl+G), and enter:
    ? CDate(0.25)
As expected, a quarter of a day is 6am - actually 6am on Dec 30 1899.

So what would you expect from:
    ? CDate(-0.25)
Would you expect that to be 6pm on the day before?
It may surpris you to learn that it is 6am on Dec 30 1899 also.

The reason is that date/time values are defined like this:
- the integer part represents the date, and
- the fraction part represents the time.

In the example above, the zero represents the date (Dec 30, 1899), and the
0.25 represents the time. Applying the same logic to -0.25, the -0
represents the date, and the 0.25 represents the time. Since 0 is the same
as -1, the date is the same. And since 0.25 is the same in both cases, 0.25
and -0.25 both represent the same date and time.

The upshot is that you should always use the built-in functions such as
DateDiff() when working with dates and times. Normal floating-point math
does not apply. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Bobk" <Bobk@discussions.microsoft.com> wrote in message
news:D72B7100-0AFD-448D-AA3A-C0A45B3E3009@microsoft.com...
>I have a report in which I am attempting to calculate elapsed time. One 
>being
> the start of work on a job, the second being the completion of the job. 
> The
> start time and the end time are entered through a form which creates a 
> record
> for each event. In my report I group the two events by day and sum them 
> using
> the sum function. In order to force subtraction (subtracting the starting
> time from the ending time) I multiply the beginning time by -1 which 
> results
> in a minus number. When the report adds the two it does not recognize the
> negative number and sums them together as if they were both positive. I 
> use
> the following routine to calculate the negative value: IIf([Start]="Y",
> ((CDbl([Trak_Date_Time]))*-1), CDbl([Trak_Date_Time]))
> Where: Trak_Date_Time is date/time. I use CDbl to convert date/time to a
> double precision value and multiply by minus 1 to get a negative number 
> for
> the stating time.If I manually subtract the converted sarting time from 
> the
> end time I get the correct elapsed time. On my report the the end time
> appears as a positive and the start time appears as a -. Summing the two
> should result in elapsed time. Sum does not recognize the negative value 
> and
> adds both together disregarding the minus sign. I'm at a loss. Anyone have 
> a
> suggestion?

Relevant Pages

  • Re: Summing Issue on Time entries
    ... How do I get the sum to display as 15:02 on a report? ... You can use a Date/Time field in the table so users can ...
    (microsoft.public.access.reports)
  • Sum of calculated fields
    ... I have a database in to which start date/time and end date/time is entered, ... from this I have calculated fields which calculate the timeDiff, ... I have tried to place the Sum() in both form footer as well as the report ...
    (microsoft.public.access.formscoding)
  • Re: "Bias Called Persistent Hurdle for Women in Sciences"
    ... A report on the underrepresentation of women in science and math by ... The report, ?Why So Few?,? ... Even if male math geniuses outnumbered female geniuses 3 to 1, ...
    (soc.men)
  • Re: Report is making too many numbers
    ... My report is grouped by Vendor, then Project, ... source query before putting it in the report so every record shows). ... A Running Sum text box accumlates its total. ... I've got a problem with Sum Totals at the end of a report, ...
    (microsoft.public.access.reports)
  • RE: Crosstab query dynamic columns ala Duane Hookom
    ... "javablood" wrote: ... I was trying to sum in the footer. ... "Duane Hookom" wrote: ... not too worried about getting this aspect of the report to work because I ...
    (microsoft.public.access.reports)