[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:

<code>

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

</code>

/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