Search code examples
vbams-accesslogicoperators

VBA do loop with double variable

I am attempting to set up a list from double type x to double type y, incremented by .001.

dblx = .093
dbly = .103

do while dblx <= dbly
    "INSERT STATEMENT for dblx"
    dblx = dblx + .001
loop

Now this works until I get to .102.

If dbly < .102 I have no issues but >= .102 the last iteration never occurs.

Am I going crazy?? I know .103 <= .103! But for some reason Access doesn't?

At first I thought it may have been the variable type, they are both doubles. I've stepped through, changed the read in values to hard set values, attempted different ranges of values... Again anything less than .102 doesn't give issues, but as soon as my "anchor value" (Max threshold) is greater than or equal to .102 then the last iteration of the do loop never works

Solution

  • Floating point arithmetic is not precise. E.g. you might get 0.103000000001213 instead of 0.103. Therefore add a little value (epsilon) to the tested end value

    Sub Test()
    Const Eps = 0.00000001
    Dim dblx As Double, dbly As Double
    
    dblx = 0.093
    dbly = 0.103
    
    Do While dblx <= dbly + Eps
        Debug.Print dblx, dbly
        dblx = dblx + 0.001
    Loop
End Sub