By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

Filling in null fields from previous records

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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...)
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.