Strange For loop problem 4

[Jerrycurl]

Put a button on a form and add this code in the click event:

Private Sub Command1_Click()
Dim x As Date
Dim starttime As Date
Dim endtime As Date

starttime = "5:00:00 AM"
endtime = "8:00:00 AM"
For x = starttime To endtime Step "00:30:00"
Debug.Print x
Next
End Sub

Run the project and click the button. You will see that each half hour from 5 AM to 8 AM is printed, as expected. Now change the AM to PM on the starttime and endtime variables and run it again. This time it only prints up to 7:30 PM. What happened to 8:00? Anyone know what's going on?

 

[Golom]

It's a rounding problem. If you display everything as numeric values rather than dates with something like

   Format ( x, "0.00000000" )

You will see that 8:00 PM equates to 0.83333333... (i.e. a repeating decimal that can't be exactly represented. The FOR loop attempts to reach that value by adding successive increments of 0.0208333... (repeating again). It does OK for the intermediate values but the last one probably evaluates to greater that the enddate out in the decimal places that would represent minor fractions of a second and the loop terminates.

In place of your FOR statement, try one like

    For x = starttime To endtime + 0.000000001 Step "00:30:00"

and the endtime will be included.

 

[LNBruno]

That's MS 4 U! LOL!

< M!ke >

 

[Jerrycurl]

Wow, that's really ridiculous. Even using the date specific function DateAdd gives the wrong result:

x = starttime
Do While x <= endtime
Debug.Print x
x = DateAdd(&quot;n&quot;, 30, x)
Loop

This works, though:

x = starttime
Do While DateDiff(&quot;n&quot;, x, endtime) >= 0
Debug.Print x
x = DateAdd(&quot;n&quot;, 30, x)
Loop

I like this way better than adding 0.000000001. Thanks for your help.

 

[jel]

You can't blame MS too much for this: it's hardly their fault that our time-measurement is not decimal.
The Date datatype actualy counts days, so hours and seconds are fractions.
If you want to count half-hours, just use an integer to count them, and convert to date only on display.

 

[Golom]

Jerrycurl

Yeah ... I like yours better too. I just threw out the &quot;0.0000001&quot; thing to illustrate that it was really a rounding issue. It wasn't meant as a real-world programming suggestion.

 

[rdroske]

How about just not using date functions to control loops?
I can't think of any good reason to do it. Using real numbers for the loop and a date function within the loop makes a lot more sense.

Ignore this if you were just posting as a curiosity.

 

[jemminger]

this is actually a relatively common occurence with floating point arithmetic, having to convert to binary to perform computations. for example in javascript if you add

var a = 12.00;
var b = 23.63;
alert( a + b );

you get
35.629999999999995

instead of
35.63

poor little cpu can't think in base 10!


=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); }