涉及三张表，
一张表存放被统计对象的名称及上偏差，下偏差信息
另外一张表是存放的测量数据
最后把统计结果存放到第三张张表
现在统计结果不正确，只有下面参数，a g有数据，且是全体个数，即所有的都被统计到，a g中
是不是我定义的数组有问题，不能存放比较用数据？导致中间过程没有比较数据大小
Public Sub CalculatePassRate()
Dim n As Long
Dim i As Long
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim f As Long
Dim g As Long
Dim m As Long
Dim FPR As Single
Dim GPR As Single
Dim V As Single
Dim arr() As String
Set rs = New ADODB.Recordset
SQLStr = "select * from BIW_Dimension_Info where Dimension_Type='Function'"
Set rs = QueryExt(SQLStr)
n = rs.RecordCount
ReDim arr(n - 1, 3)
For i = 0 To n - 1
arr(i, 0) = rs.Fields("Dimension_Name")
arr(i, 1) = rs.Fields("AXIS_Name")
arr(i, 2) = rs.Fields("Plus_Tol")
arr(i, 3) = rs.Fields("Minus_Tol")
Next
For i = 0 To n - 1
SQLStr = "select * from BIW_CMM_Data where Dimension_Type='Function' and Dimension_Name='" & arr(i, 0) & "' And Measure_ID ='" & MeasureID & "'"
Set rs = QueryExt(SQLStr)
If Not rs.EOF Then
If Not IsNull(rs.Fields(arr(i, 1) & "_Dev")) Then
V = Val(rs.Fields(arr(i, 1) & "_Dev"))
If V > Val(arr(i, 2)) Or V < Val(arr(i, 3)) Then
a = a + 1
ElseIf V >= Val(((arr(i, 2) + arr(i, 3)) / 2) * 0.25 + arr(i, 3)) And V >= Val((arr(i, 3) - (arr(i, 2) + arr(i, 3)) / 2) * 0.25) Then
b = b + 1
Else
c = c + 1
End If
End If
End If
Next
d = a + b + c
FPR = Format((b + c) / d, "0.00")
SQLStr = "select * from BIW_Dimension_Info where Dimension_Type='General'"
Set rs = QueryExt(SQLStr)
n = rs.RecordCount
ReDim arr(n - 1, 3)
For i = 0 To n - 1
arr(i, 0) = rs.Fields("Dimension_Name")
arr(i, 1) = rs.Fields("AXIS_Name")
arr(i, 2) = Val(rs.Fields("Plus_Tol"))
arr(i, 3) = Val(rs.Fields("Minus_Tol"))
Next
For i = 0 To n - 1
SQLStr = "select * from BIW_CMM_Data where Dimension_Type='General' and Dimension_Name='" & arr(i, 0) & "' and Measure_ID ='" & MeasureID & "'"
Set rs = QueryExt(SQLStr)
If Not rs.EOF Then
If Not IsNull(rs.Fields(arr(i, 1) & "_Dev")) Then
V = Val(rs.Fields(arr(i, 1) & "_Dev"))
If V > Val(arr(i, 2)) Or V < Val(arr(i, 3)) Then
e = e + 1
ElseIf V >= Val(((arr(i, 2) + arr(i, 3)) / 2) * 0.25 + arr(i, 3)) And V >= Val((arr(i, 3) - (arr(i, 2) + arr(i, 3)) / 2) * 0.25) Then
f = f + 1
Else
g = g + 1
End If
End If
End If
Next
m = e + f + g
GPR = Format((f + g) / m, "0.00")
Set rs = QueryExt("select * from BIW_CMM_Info where Measure_ID='" & MeasureID & "'")
rs.Fields("FunDim_Pass_Rate") = FPR
rs.Fields("FunDim_Pass_Num") = b
rs.Fields("FunDim_Pass_Con") = c
rs.Fields("FunDim_Pass_No") = a
rs.Fields("GenDim_Pass_Rate") = GPR
rs.Fields("GenDim_Pass_Num") = f
rs.Fields("GenDim_Pass_Con") = g
rs.Fields("GenDim_Pass_No") = e
rs.Update
rs.Close
End Sub
Public Sub SQLExt(SQLStr As String)
Dim cmd As New ADODB.Command
Connect
Set cmd.ActiveConnection = conn
cmd.CommandText = SQLStr
cmd.Execute
Set cmd = Nothing
Disconnect
End Sub
Public Function QueryExt(ByVal SQLStr As String) As ADODB.Recordset
Dim rs As New ADODB.Recordset
Connect
Set rs.ActiveConnection = conn
rs.CursorType = 1
rs.LockType = 3
rs.Open SQLStr
Set QueryExt = rs
End Function
1 个解决方案
#1
ReDim arr(n - 1, 3) 都已经把数组内容给清理掉了还比较个啥。
#1
ReDim arr(n - 1, 3) 都已经把数组内容给清理掉了还比较个啥。