[AccessD] Loop Until Date=Date

Thanks Everyone,

I never thought of 30 minutes as 1/48 of a day.  It does feel better knowing that my looking at 2 identical things...and them not being = was my ignorance and not my insanity.  

Is this 'issue' just in access...maybe excel...and what about SQL Server?

Thanks,


Mark A. Matte


> Date: Wed, 23 Jan 2008 18:30:33 +0100
> From: Gustav at cactus.dk
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Loop Until Date=Date
>
> Hi Mark
>
> No, it is just that a date time value really is a Double, and subtracting Doubles will cause rounding errors sooner or later.
> And you do perform a subtraction because the comparison
>
> RecordDT = RecordDT_end
>
> is performed behind the scene as
>
> RecordDT - RecordDT_end = 0
>
> Thus, always either use the DateSerial, TimeSerial, DateDiff, DateAdd, etc functions when handling date times, or be very careful with rounding errors, or test for an interval (comparing only>= or <= as Drew suggests is only halfway safe)
>
> Abs(RecordDT - RecordDT_end) <= 0.0001
>
> Here are the functions we use to round a date time value correctly:
>
> 
>
> Private Const clngSecondsPerDay As Long = 24& * 60& * 60&
>
> Public Function DateTimeRound( _
> ByVal datTime As Date) _
> As Date
>
> ' Returns datTime rounded off to the second by
> ' removing a millisecond portion.
> '
> ' 2006-01-23, Cactus Data ApS, CPH
>
>
> Call RoundSecondOff(datTime)
>
> DateTimeRound = datTime
>
> End Function
>
> Private Sub RoundSecondOff( _
> ByRef datDate As Date)
>
> ' Rounds off datDate to the second by
> ' removing a millisecond portion.
> '
> ' 2006-01-23, Cactus Data ApS, CPH
>
> Dim lngDate As Long
> Dim lngTime As Long
> Dim dblTime As Double
>
> ' Get date part.
> lngDate = Fix(datDate)
> ' Get time part.
> dblTime = datDate - lngDate
> ' Round time part to the second.
> lngTime = Fix(dblTime * clngSecondsPerDay)
> ' Return date part and rounded time part.
> datDate = CVDate(lngDate + lngTime / clngSecondsPerDay)
>
> End Sub
>
> 
>
> /gustav
>
>
>>>> markamatte at hotmail.com 23-01-2008 17:59:50>>>
>
> Ok...its now clear as mud...
>
> RecordDT = 12/17/2007 4:30:00 PM
> RecordDT_end =12/17/2007 4:30:00 PM
> CDbl(RecordDT)=39433.6875
> CDbl(RecordDT_End)=39433.6875
> CDbl(RecordDT-RecordDT_End)=3.63797880709171E-11
>
> I'm not liking computers very much right now...so...since I added 30 minutes I have this problem?
>
> ...and Going forward...I should never ask if 2 DateTime values are equal...I should compare using datediff and seconds?
>
> Thanks,
>
> Mark A. Matte
>
>
>> Date: Wed, 23 Jan 2008 17:35:38 +0100
>> From: Gustav at cactus.dk
>> To: accessd at databaseadvisors.com
>> Subject: Re: [AccessD] Loop Until Date=Date
>>
>> Hi Mark
>>
>> But what about: CDbl(RecordDT - RecordDT_end)?
>>
>> /gustav
>>
>>>>> markamatte at hotmail.com 23-01-2008 17:31:30>>>
>>
>> That was the first thing I did...both are showing 12/17/2007 4:30:00 PM...but the "=" fails.
>>
>> Thanks,
>>
>> Mark A. Matte
>>
>>
>>> Date: Wed, 23 Jan 2008 11:22:05 -0500
>>> From: fuller.artful at gmail.com
>>> To: accessd at databaseadvisors.com
>>> Subject: Re: [AccessD] Loop Until Date=Date
>>>
>>> I would add a Debug.Print statement at the top or bottom of the loop to
>>> print out both values in Long Date format so you can see all the particulars
>>> of both values. That should reveal the difference, and why equality is
>>> failing.
>>>
>>> A.
>>>
>>> On 1/23/08, Mark A Matte wrote:
>>>>
>>>>
>>>> Hello All,
>>>>
>>>> Thanks for the suggestions. I found last night that "DateDiff("s",
>>>> RecordDT, RecordDT_end) = 0" would work...but I just can't seem to
>>>> understand why they were not equal. I tried the suggestion below...but it
>>>> failed as well. I also tried "If RecordDT>= RecordDT_end"...this
>>>> works...but scares me because I don't know why it works and "=" does not. I
>>>> think I will just use the datediff approach...but am still curious...
>>>>
>>>>
>>>> Thanks,
>>>>
>>>> Mark A. Matte
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Connect and share in new ways with Windows Live.
http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_012008