ms access vba: sorting is not working -

i trying sort elements of field manually without using built in function. below code

    function test(myval variant) variant         dim rst dao.recordset         dim arr() double         dim arr_lenght long         dim integer         dim j integer         dim k integer         dim temp double         dim count integer         dim index integer          set rst = currentdb.openrecordset("select [salary (t) total] salaryt")          count = 1         index = 0         rst.movefirst          while not rst.eof             on error resume next             arr(count) = cdbl(rst![salary (t) total])             count = count + 1             rst.movenext         loop          arr_length = ubound(arr) - 1  'this sorting algorithm          = 1 arr_length             j = (i + 1) arr_length                    if arr(i) < arr(j)                     temp = arr(i)                     arr(i) = arr(j)                     arr(j) = temp                    end if             next j         next          k = lbound(arr) ubound(arr)             if arr(k) = cdbl(myval )                 index = k                 exit             end if         next k          test = index          end function 

this output 0 since set index @ beginning 0. in last loop change index value value of k , looks not changing value. wrong code. please me fix it. thanks.

some example data salaryt:

id  name           salary (t) total  1   test name1       55,234.30 2   test name2       2,322.29  

salary (t) total save values text. , later in arr array take values , keep them double value.

myval send value salary (t) total column. example function call be

test(55,234.30) test(2,322.29) 

i think problem not in sort, in assignment array. don't initialize elements of array. if take out line:

on error resume next 

you see function fails at:

arr(count) = cdbl(rst![salary (t) total]) 

with error "subscript out of range".

if add subscript dim arr() works intended. if don't know size of array can count records first redim variable. (or wait declare until after count records. know use rst.countrecords, experience has been sketchy @ best, prefer count records other way, 1 quick example.)

count = 0 rst!movefirst while not rst.eof     count = count + 1 loop redim arr(count) double