มีคนถามไว้ที่
http://www.quicktechusa.com/msgboard/wwwboard.pl?read=15514

คำถามคือ

I have a table with text in a column A and columnB such as
A............B
123
124
124....Thru
128
135

Whenever the word Thru appears, I want to expand the numbers such as

123
124
125
126
127
128
135

I can do this in Excel, but can it be done in ACCESS??

Richard M


ผมได้ตอบโค้ดตัวนี้ไป

Sub FillThru()
Dim dbs As Database, rst As Recordset
Dim intCount As Integer, I As Integer, J As Integer
Dim intMax As Integer, intMin As Integer

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("Select * From Table1 Order By A, B;")

If Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
For I = 1 To rst.RecordCount
'rst(0) = Format(I, "0000")
Debug.Print rst("A")
If rst("B") = "Thru" Then
intMin = Val(rst("A"))
Debug.Print intMin
rst.Move 1
intMax = Val(rst("A")) - 1
Debug.Print intMax
rst.Move -1
For J = 0 To intMax - intMin - 1
Debug.Print "Missing -- "; (intMin + 1) + J
rst.AddNew
rst("A") = intMin + 1 + J
rst.Update
Next J
End If
rst.MoveNext
Next I
dbs.Execute "Delete * From Table1 Where B='Thru';"
Else
Exit Sub
End If

rst.Close
dbs.Close

End Sub

ส่วนตัวอย่างจริงอยู่ที่
http://agserver.kku.ac.th/basiceng/fillnumbersingaps.zip ครับ