al*****@cox.net wrote in message news:<40***************@news.west.cox.net>...
Hello,
I have a table that has null fields that need to be filled in with the value of the previous record
(example below)
id date
1 2/2/02
2
3 4/4/02
4
5
6 3/303
7
What I need to do is loop through the records and if the filed is null--insert the value of the
rprevious record into it. There may be one null or there may be many. I don't have a clear
understanidng of loops--can this be done? I inherited this dataset and need to clean it up--there
are 6,000 records in this table that I need to fix. I need to do it for different fields but used
the date field as the example
Thanks
bob
Bob,
here's an example that uses a date field.
Option Compare Database
Option Explicit
Public Sub FillInTheBlanks()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dtLast As Date
Set db = CurrentDb
Set rs = db.OpenRecordset("tblDates", dbOpenTable)
rs.MoveFirst '--Go to the first record
dtLast = rs.Fields("DateDue").Value '--stuff the date into a
variable
rs.MoveNext '--go to the next record
Do Until rs.EOF
If IsNull(rs.Fields("DateDue")) Then
rs.Edit '--put record in edit
mode.
rs.Fields("DateDue") = dtLast '--assign the new date
value from the previous record.
rs.Update '--save the new date to
the record
Else
dtLast = rs.Fields("DateDue") '--if the new record's
value is not null, save it to write to the next blank date
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "Done! "
End Sub
If you wanted, you could probably change it so that the dtLast
variable to a Variant type, so you could put any kind of value in
there. then just pass the field you want to update and the tablename,
and you could make it really generic. (If you really wanted to go to
town, you could create a table of tables and fields that need to be
updated, loop through that, and pass the values to this Sub...)
