Re: Sum not recoginzing a negative number

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

Date: Tue, 7 Dec 2004 00:20:54 +0800

Using your example in the Immediate Window:
    ? CDate(38327.06278935), CDate(-38327.06278935)
    12/6/2004 1:30:25 AM 01/22/1795 1:30:25 AM

Now subtract 3 hours (0.125 of a day). You expect the result to be
10:30:25pm on the day before in both cases? Try it:
    ? CDate(38327.06278935 - .125), CDate(-38327.06278935 - .125)

You cannot treat date/times as floating point numbers, particularly with
negative values. 

-- 
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:004B0C77-DBEC-46E8-B5BB-703E0D3D4F0E@microsoft.com...
> Thanks for the response. I thought that CDbl was supposed to result in a
> double precision number, which it appears to do. My particular example has 
> a
> date of 12/6/2004 2:23:35 AM for the end time and 12/6/2004 1:30:25 AM for 
> a
> start time.
> After I convert these times to Double precision I get 38327.099710648 for
> the end time and 38327.06278935 for the start time. Multiplying by -1 
> yields
> -38327.06278935. Adding these together yields a difference of .036921298.
> This multiplied by 24 =.886111152  which is equal to the hours difference,
> which converts to 53 minutes and 10 seconds. If this works so well when I
> compute it manually it should work in the report. The only problem I see 
> is
> that Sum doesn't recognize my negative value and just goes ahead and adds 
> it
> as though it were a positive number. Why?
>
> "Allen Browne" wrote:
>
>> 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: Filter in a report?
    ... Are you trying to Sum the Hours where the Timebase is equal to 02 and then ... you are multiplying everything else in the equation by zero. ... >> MS Access MVP ... I have a report bound to a query. ...
    (microsoft.public.access.reports)
  • Re: calculating time with IF statement
    ... =sum() doesn't really add anything to your formulas. ... True in a multiplication will be the same as multiplying by 1. ... I only want to subtract the 30 minutes if cell A3 is greater than ...
    (microsoft.public.excel)
  • Re: Percent on sum total
    ... The problem is that the report will be run from data that already exists ... formula to some how filter one country from the other on the report. ... Then just multiply InvAmt by that value to get what you requested... ... then subtract that value from InvAmt.) ...
    (microsoft.public.access.reports)
  • RE: Subtract on a report based on QRY cross table
    ... "Duane Hookom" wrote: ... cross tab query, this one, the sub report, i would like, on Total, to show ... what i would like is to subtract both records on my crosstab report ... ... PlusMinus field in "a table with those accounts only with groups number" that ...
    (microsoft.public.access.reports)
  • Re: Comparing sets of data, where criteria met, sum certain column
    ... I would like the results in column I (the lookup) and J (sum of the values). ... yes, "subtract the value in Column D, in the row of the Column A item, ... "Otto Moehrbach" wrote: ...
    (microsoft.public.excel.worksheet.functions)
Loading