数据库字符串连接和数字统计问题，比较难一点。 - Office中国论坛/Access中国论坛

Private Sub Command0_Click()
Dim rs As New ADODB.Recordset
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim strSQL As String
Dim sSQL As String
Dim str As String
Dim lng As Long
Dim i As Integer
On Error GoTo Command0_Click_Error
Set cnn = CurrentProject.Connection
CurrentDb.Execute "delete from JG"
strSQL = "select distinct bb from 数据"
rs.Open strSQL, cnn, adOpenKeyset, adLockReadOnly
Do While Not rs.EOF
strSQL = "select cc,dd from 数据 where bb='" & rs.Fields(0) & "'"
rst.Open strSQL, cnn, adOpenKeyset, adLockReadOnly
Do While Not rst.EOF
str = str & rst.Fields("cc") & "-" & rst.Fields("dd") & "/"
lng = lng + rst.Fields("dd") - rst.Fields("cc") + 1
rst.MoveNext
Loop
rst.Close
i = i + 1
If str <> "" Then
str = Left(str, Len(str) - 1)
End If
sSQL = "insert into JG(AA,BB,FF,GG)VALUES(" & i & ",'" & rs.Fields(0) & "'," & lng & ",'" & str & "')"
CurrentDb.Execute sSQL
rs.MoveNext
lng = 0
Loop
DoCmd.OpenTable "JG", acViewNormal
rs.Close
Set rs = Nothing
Set rst = Nothing
Set cnn = Nothing
On Error GoTo 0
Exit Sub
Command0_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
End Sub

复制代码