Using VB to make calculations on a report

[proben930]

I have a rather complicated database that processes incoming tax bills. All the data submitted is entered into a form, and I used some complex calculations to tell if there is a difference between what they entered and what it should be.

Now I'm trying to generate a report based on some of this... and I can't get it to recalculate the values on the report. The calculations are rather complex so they are in VB. I'll paste it in but don't laugh at me because I'm sure its fairly crude

How can i make this get recalculated when I open a report? If i paste it into the on open event it generates an error that the dlookup functions are returning null. TIA!





Dim Penalty As Currency

Penalty = 0

If DateDiff("d", [due date], [date paid]) > 0 Then Penalty = 0.1 * [Test Total Tax Due]
If DateDiff("d", [due date], [date paid]) > 30 Then Penalty = 0.2 * [Test Total Tax Due]
If DateDiff("d", [due date], [date paid]) > 60 Then Penalty = 0.3 * [Test Total Tax Due]
If DateDiff("d", [due date], [date paid]) > 90 Then Penalty = 0.4 * [Test Total Tax Due]
If DateDiff("d", [due date], [date paid]) > 120 Then Penalty = 0.5 * [Test Total Tax Due]
If DateDiff("d", [due date], [date paid]) > 0 And Penalty < 10 Then Penalty = 10


Test_Penalty = Penalty

Dim beg_intrate As String
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 String
Dim totalint As String
Dim checkend As Boolean


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

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

If [date paid] <= beg_intrate_end Then begint = (DateDiff("d", [due date], [date paid]) * beg_intrate)
If [date paid] > beg_intrate_end Then begint = (DateDiff("d", [due date], beg_intrate_end) * beg_intrate)

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

Text125 = DateDiff("d", end_intrate_begin, [date paid])
Text127 = end_intrate

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

totalint = begint + midint + endint
totalint = Round(totalint, "9")
If [date paid] < [due date] Then totalint = 0
[Test Interest] = Round([Test Total Tax Due] * totalint, 2)
endlabel:

 

[RichO]

To begin with, try putting the code in the On Activate event and go from there. I believe that the On Open event occurs before the data is loaded into the report.

 

[proben930]

I think thats the main problem....that the code is trying to calculate stuff based on data that hasn't loaded yet. How can I make it happen after data is loaded?

 

[GUIDO22]

Add your code to the Detail_Format event() - that should do the trick.