Down-filling nulls with previous entry Code Help needed

[petrosky]

Hi,

I have a problem.

Data in my table is looks like this.

Field8

Bob
John
-->null value should be John
-->null value should be John
Harry
-->null value should be Harry
Mike
Mary
Jane

I need a way for the nulls to be filled in with the value before/above them.

I have VERY little VB programming skill so any ideas or help will be much appreciated.

Regards,

Peter. Remember- It's nice to be important,
but it's important to be nice

 

[jgoodman00]

What is the primary key in the table? James Goodman

 

[petrosky]

There currently is no primary key currently.

It is data from a text import that seems to be quite limited in it's format (hence the gaps in data)

I can opt for an autonumber PK if required though.

regards,

peter Remember- It's nice to be important,
but it's important to be nice

 

[petrosky]

To update,

Many thanks to Paul Bricker for this elegant solution...


Function GetName()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Translog", dbOpenDynaset)
Dim strValue As String
rst.MoveFirst
Do Until rst.EOF
If Not IsNull(rst!Field8) Then
strValue = rst!Field8
rst.MoveNext
Else
rst.Edit
rst!Field8 = strValue
rst.Update
rst.MoveNext
End If
Loop

End Function

The function is simply called from a command button.


Remember- It's nice to be important,
but it's important to be nice

 

[jgoodman00]

Thats similar to the method I was going to suggest. However, I was worried about the order in which the recordset would be opened, fearing each record might not be opened in the sequence you displayed above... James Goodman

 

[petrosky]

Hi James,

Yes, according to the code above if the first value in the table is null I wil get no records in the record set.

Peter. Remember- It's nice to be important,
but it's important to be nice