Sub Update()
Dim rst As New ADODB.Recordset, FNum As Double, FSumNum As Double, FCount As Long, FMaxP As Double, FTdays As Long, FBudget As Long
Dim HiPay As Double, Lopay As Double, FPitDiff As Double, FRange As Double, FPercent As Double, FCoutoff As Double, FWscores As Double
FNum = DLookup("Benchmark", "Step13_Benchmark")
FTdays = DLookup("Totaldays", "Step13_Benchmark")
FMaxP = DLookup("MaxPoint", "Step13_Benchmark")
FBudget = DLookup("Budget", "P4PVars")
FPercent = DLookup("HiUp", "P4PVars")
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "SELECT * FROM Step12_Eligible_List order by P22 by desc "
.Open
Do Until .EOF
FSumNum = FSumNum + .Fields("MADays")
FWscores = FWscores + .Fields("P22") * .Fields("MADays")
If FSumNum > FNum Then
FCount = .AbsolutePosition
FCutOff = .Fields("P22")
FRange = FMaxP - FCutOff
FPitDiff = FSumNum + (FWscores - FCutOff * FSumNum) / FRange
Lopay = Round(FBudget / FPitDiff, 2)
HiPay = Round(Lopay * FPercent, 2)
Exit Do
End If
.MoveNext
Loop
End With
With Me
.Text64 = FCount
.Text72 = FSumNum
.Text70 = FSumNum / FTdays
.LowPay = Format(Lopay, "0.00")
.HighPay = Format(HiPay, "0.00")
.CutOffP = FCutOff
End With
End Sub
错误的部分是这一段 .Source = "SELECT * FROM Step12_Eligible_List order by P22 desc "
如果去掉 desc，运行错误代码是 “无效使用NULL”。 DEBUG 显示在 FWscores = FWscores + .Fields("P22") * .Fields("MADays")
可是我的表里没空值呀。
如果加上DESC, 可以运行，但出不来正确结果，正确是加到 第64行退出，得出总值，可是它出来得是加了63行，有一行得字段数值没加上。。。。
