Fill in blank lines with info from previous record

[CMcGrann]

I have a table that I need to fill in the blank lines with the info from the
previous record, is there a query I can use to fill in these blank spaces?
Currently: What I want:
SOS SOS
SOS
SOS
COD COD
COD
BCT BCT
BCT

 

[Allen Browne]

You must have a primary key field that defines the order of the records.
Otherwise the concept of 'previous' row is meaningless. Add an AutoNumber if
necessary.

This example assumes the table named Table1 with 2 fields:
- F1 is the field you have already;
- ID is the autonumber.

Type an expression like this into the Field row in query design (all on one
line):
PriorValue: (SELECT TOP 1 F1
FROM Table1 AS Dupe
WHERE dupe.F1 Is Not Null
AND Dupe.ID < Table1.ID
ORDER BY ID DESC)

Once you verify that this is okay, change the query to an Update query
(Update on Query menu.) Access adds an Update row to the query design grid.
Then move everything inside the brackets (including the brackets) into the
Update row under F1, and in the Criteria row, enter:
Is Not Null
Run the query to fill in the blank rows.

A query statement like that inside another query is called a subquery. If
that's a new concept, here's some more info:
http://allenbrowne.com/subquery-01.html

 

[Stefan Hoffmann]

hi,

I have a table that I need to fill in the blank lines with the info from the
previous record, is there a query I can use to fill in these blank spaces?

As there is no such thing as a previous record, no there is no query.

Currently: What I want:
SOS SOS
SOS
SOS
COD COD
COD
BCT BCT
BCT

May use something like this:

Dim rs As DAO.Recordset

Dim PreviousValue As Variant

Set rs = CurrentDb.OpenRecordset("yourTableOrQuery", dbOpenDynamic)
If Not rs.Bof And Not rs.Eof Then
Do While Not rs.Eof
If Len(Trim(rs![Field] & vbNullString)) = 0 Then
rs.Edit
rs![Field] = PreviousValue
rs.Update
Else
PreviousValue = rs![Field]
End If
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing



mfG
--> stefan <--