"you cancelled previous operation" problem

[proben930]

I have a set of code in a form that I really need to move into a function to use in other places as well. It works perfectly in the form, but when i put it into a module i get the error "you cancelled the previous operation". What it does is calculate interest based on an interest rate that fluxuates over time. I'll paste it up real quick.. but don't make fun of it Why do I get this problem? I changed all the variable names to match is all I did. TIA!

Option Explicit
Option Compare Database

Public Function CalcInterest(grossreceipts As Currency, duedate As Date, datepaid As Date) As Currency

Dim beg_intrate As Long
Dim beg_intrate_begin As Date
Dim beg_intrate_end As Date
Dim end_intrate_begin As Date
Dim end_intrate_end As Date
Dim end_intrate As Long
Dim totalint As String
Dim checkend As Boolean
Dim begint, midint, endint As Currency

beg_intrate_begin = DLookup("[beginning date]", "[interest rates]", "[beginning date] <= duedate AND duedate <= [ending date]")
beg_intrate_end = DLookup("[ending date]", "[interest rates]", "[beginning date] <= duedate] AND duedate <= [ending date]")
beg_intrate = DLookup("[interest rate]", "[interest rates]", "[beginning date] <= duedate] AND duedate <= [ending date]")

end_intrate_begin = DLookup("[beginning date]", "[interest rates]", "[beginning date] <= datepaid AND [datepaid <= [ending date]")
end_intrate_end = DLookup("[ending date]", "[interest rates]", "[beginning date] <= datepaid AND [datepaid <= [ending date]")
end_intrate = DLookup("[interest rate]", "[interest rates]", "[beginning date] <= datepaid AND [datepaid <= [ending date]")


If datepaid <= beg_intrate_end Then begint = (DateDiff("d", duedate, datepaid) * beg_intrate)
If datepaid > beg_intrate_end Then begint = (DateDiff("d", duedate, beg_intrate_end) * beg_intrate)

checkend = IsNull(DSum("[interest rate]", "[interest rates]", "duedate < [beginning date] AND datepaid > [ending date]"))

If datepaid > beg_intrate_end Then
    endint = (DateDiff("d", end_intrate_begin, datepaid) + 1) * end_intrate
    If checkend = False Then midint = DSum("[total int rate]", "[interest rates]", "duedate < [beginning date] AND datepaid > [ending date]")
    End If

totalint = begint + midint + endint
totalint = Round(totalint, "9")
If datepaid < duedate Then totalint = 0
CalcInterest = Round(grossreceipts * totalint, 2)
End Function

 

[namliam]

I am sorry to say but there are a number of problems with this code

1)
Dim begint, midint, endint As Currency
You offcourse know that your only declaring endint as a currency and that begint and midint are variants?! To declare all as a Currency you must do
Dim begint as currency, midint as currency, enint as currency

2)
beg_intrate_begin = DLookup("[beginning date]", "[interest rates]", "[beginning date] <= duedate AND duedate <= [ending date]")
I am guessing you want to search for the duedate you pass to the function?! If so this is not /should not be working as hoped.... Proper way:
beg_intrate_begin = DLookup("[beginning date]", "[interest rates]", "[beginning date] <= #" & duedate & "# AND #" & duedate & "# <= [ending date]")
Same with all dlookups

3)
end_intrate_begin = DLookup("[beginning date]", "[interest rates]", "[beginning date] <= datepaid AND [datepaid <= [ending date]")
there is a [ to many.... and 2) but for datepaid

4)
If datepaid <= beg_intrate_end Then begint = (DateDiff("d", duedate, datepaid) * beg_intrate)
If datepaid > beg_intrate_end Then begint = (DateDiff("d", duedate, beg_intrate_end) * beg_intrate)
This might be better served with
If ... then
...
else
...
endif

5)
Its a bit late ... but you shouldnt use spaces in your field names....

Regards