Update Null Values

[Martin]

Hello,

I have a table which I have imported from a main frame. There are two
columns one with the unique identifier and one with a number, an example is
below:

ID Value
N1 15
26
38
N2 73
85
46

The problem I have is I need the ID above to be copied into the rows where
the ID is null until the ID is not null. So the table will look like this:

ID Value
N1 15
N1 26
N1 38
N2 73
N2 85
N2 46

Can someone advise on the best way to do this? I think it needs a do loop
function but I can't think how to write this.

Many thanks,

Martin

 

[Ofer Cohen]

Forgive me for any mistake, I can't try this code, in my current computer
there is no Access installed

Dim MyDB as Dao.DataBase, MyRec As Dao.RecordSet
Dim OldID as String
OldID = ""
Set MyDb = CurrentDb
Set MyREc = MyDb.OpenRecordSet("Select * From TableName")
While Not MyRec.Eof
If Len(Trim(MyRec!ID) & "") = 0 And OldID <> "" Then
MyRec.Edit
MyRec!ID = OldID
MyRec.Update
Else
OldID = MyRec!ID
End If
MyRec.MoveNext
Wend

*****
This code might give you a starting point

 

[Ofer Cohen]

One more thing,
You might want to sort the order of the records, in that case add the sort
order to the record set.

It's important, so the records wont fill up in the wrong order

 

[Tim Ferguson]

ID Value
N1 15
26
38
N2 73
85
46

The problem I have is I need the ID above to be copied into the rows
where the ID is null until the ID is not null. So the table will look
like this:

I would do this in the original text file before importing it into a table,
since the record order will be critical and cannot be guaranteed once it's
in Access.

If you have a scriptable text editor, it shouldn't be too hard. Anyone for
perl or sed?


Hope that helps


Tim F