求区间对应值

自定义函数ADO查找

Function getPrice(ByVal myFiled As String, ByVal Weight As Double) As Double
    On Error GoTo Err
    
    Dim rst As New ADODB.Recordset
   
    rst.Open "区域重量价格表", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
    rst.MoveFirst
    Do Until rst.EOF
        If rst.Fields(0).Value = myFiled Then
            Select Case Weight
            Case 0 To 0.5
                getPrice = rst.Fields(1).Value
            Case 0.5 To 1
                getPrice = rst.Fields(2).Value
            Case 1 To 2
                getPrice = rst.Fields(3).Value
            Case Else
                getPrice = 0
            End Select
        End If
        rst.MoveNext
    Loop
    
    rst.Close: Set rst = Nothing
    
Exit_ERR:
    Exit Function
    
Err:
    MsgBox Err.Description
    Resume Exit_ERR

End Function

点击下载此附件