菜鸟 发表于：2013-06-07 12:01:57
由列变为行
能不能帮我看一下这代码那出错了，谢谢！
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim i As Long
i = 1
CurrentDb.Execute "delete * from S"
Set rst = CurrentDb.OpenRecordset(" SELECT DepartmentName,CardNo,HolderNo, IODate FROM S1 ORDER BY HolderNo, IODate", , vbReadOnly)
Set rst2 = CurrentDb.OpenRecordset("S")
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
Set rst1 = CurrentDb.OpenRecordset("select IOTime from S1 where HolderNo='" & rst!HolderNo & "'and IODate= # " & Format(rst!IODate, "yyyy-mm-dd") & " #")
rst1.MoveLast
rst1.MoveFirst
rst2.AddNew
rst2!HolderNo = rst!HolderNo
rst2!IODate = rst!IODate
rst2!DepartmentName = rst!DepartmentName
rst2!CardNo = rst!CardNo
rst2.Update
rst2.MoveLast
Do Until rst1.EOF
rst2.Edit
rst2(i + 4) = rst1!IOTime
rst2.Update
i = i + 1
rst1.MoveNext
Loop
i = 1
rst.MoveNext
Loop
rst.Close: Set rst = Nothing
rst1.Close: Set rst1 = Nothing
rst2.Close: Set rst2 = Nothing
End Sub
S
|
DepartmentName
|
HolderNo
|
CardNo
|
HolderName
|
IODate
|
IOTime1
|
IOTime2
|
IOTime3
|
IOTime4
|
|
出口部
|
0130
|
0108
|
张三
|
2012-10-18
|
12:26:07
|
13:25:46
|
17:25:37
|
|
|
出口部
|
0130
|
0108
|
张三
|
2012-10-19
|
08:30:02
|
12:26:42
|
13:25:36
|
17:25:09
|
|
出口部
|
0131
|
0109
|
李四
|
2012-10-19
|
08:30:02
|
12:26:42
|
13:35:36
|
17:25:09
|
老师，我是要这样的效果，上面运行都重复了！
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim i As Long
CurrentDb.Execute "delete * from S"
Set rst = CurrentDb.OpenRecordset(" SELECT DepartmentName,HolderName,CardNo,HolderNo, IODate FROM S1 GROUP BY S1.DepartmentName, S1.CardNo, S1.HolderNo,S1.HolderName, S1.IODate ORDER BY HolderNo, IODate", , vbReadOnly)
Set rst2 = CurrentDb.OpenRecordset("S")
' rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
Set rst1 = CurrentDb.OpenRecordset("select IOTime from S1 where HolderNo='" & rst!HolderNo & "'and IODate= # " & Format(rst!IODate, "yyyy-mm-dd") & " #")
' rst1.MoveLast
rst2.AddNew
rst2!HolderNo = rst!HolderNo
rst2!IODate = rst!IODate
rst2!DepartmentName = rst!DepartmentName
rst2!HolderName = rst!HolderName
rst2!CardNo = rst!CardNo
' rst2.MoveLast
rst1.MoveFirst
i = 5
Do Until rst1.EOF
' rst2.Edit
rst2(i) = rst1!IOTime
' rst2.Update
i = i + 1
rst1.MoveNext
Loop
rst.MoveNext
rst2.Update
Loop
rst.Close: Set rst = Nothing
rst1.Close: Set rst1 = Nothing
rst2.Close: Set rst2 = Nothing
End Sub
Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim i As Long
CurrentDb.Execute "delete * from S"
Set rst = CurrentDb.OpenRecordset(" SELECT DepartmentName,HolderName,CardNo,HolderNo, IODate FROM S1 GROUP BY S1.DepartmentName, S1.CardNo, S1.HolderNo,S1.HolderName, S1.IODate ORDER BY HolderNo, IODate", , vbReadOnly)
Set rst2 = CurrentDb.OpenRecordset("S")
' rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
Set rst1 = CurrentDb.OpenRecordset("select IOTime from S1 where HolderNo='" & rst!HolderNo & "'and IODate= # " & Format(rst!IODate, "yyyy-mm-dd") & " #")
' rst1.MoveLast
rst2.AddNew
rst2!HolderNo = rst!HolderNo
rst2!IODate = rst!IODate
rst2!DepartmentName = rst!DepartmentName
rst2!HolderName = rst!HolderName
rst2!CardNo = rst!CardNo
' rst2.MoveLast
rst1.MoveFirst
i = 5
Do Until rst1.EOF
' rst2.Edit
rst2(i) = rst1!IOTime
' rst2.Update
i = i + 1
rst1.MoveNext
Loop
rst.MoveNext
rst2.Update
Loop
rst.Close: Set rst = Nothing
rst1.Close: Set rst1 = Nothing
rst2.Close: Set rst2 = Nothing
End Sub
总记录：4篇 页次：1/1 9 1 :
