Simulating Fill-Down in Access

[elbastador]

I'm importing some text files into Access. Some of the rows are missing data and I'm trying to copy the value from the row above into all the blank rows. I know I can do this in Excel but I'm trying to figure out how to do it in Access, as some of the files are too large to be imported into Excel. So essentially if I have a table with 3 columns (1st being an autonumber) like this:

1 1 35010
2
3
4 2 35015
5
6

End result is that I would like this:
1 1 35010
2 1 35010
3 1 35010
4 2 35015
5 2 35015
6 2 35015

Is something like this even possible? I'm thinking that I could do something like this in VB, but I'm not as familiar with VB in Access as I am in Excel so I'm not sure how I would set that up. Any thoughts? Thanks!

 

[parry]

Hi, this would need to be adapted to indicate your table name and the fields your interrogating. In this example theres a Table2 that has an autonum field called ID and Im wanting to sort in ID number order and if the text field repaired_By is blank, then populate the data from the previous row that had something in the repaired_by field. This code requires you to set a reference so when your in the VBE select Tools, References then select the Microsoft DAO Object library object indicated and this will have a tick against it. The code needs this library to access the recordset object. hth

Sub PopulateData()
Dim Rs As Recordset, strSQL As String, CurData As Variant
'Note: Set a reference to the Microsoft DAO Object library via Tools | References

'Amend this to indicate the table and sort by the autonumber id
strSQL = "SELECT * FROM TABLE2 ORDER BY ID"

'Opens a recordset (like a query) to the table
Set Rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)


'Loop through the records in the table and if the previous row didnt have data in the
'Repaired_By field then populate the previous Repaired_By data. Assumes the first record is
'populated with info
Rs.MoveLast
Rs.MoveFirst
Do
    If Rs!repaired_By = "" Or IsNull(Rs!repaired_By) Then
        'This field is blank so populate the info from the variable
        Rs.Edit
        Rs!repaired_By.Value = CurData
        Rs.Update
    Else
        'This field isnt blank so store the info in the variable in case the next record is blank
       CurData = Rs!repaired_By.Value
    End If
    Rs.MoveNext
Loop While Not Rs.EOF

'Remove the recordset object from memory
Rs.Close
Set Rs = Nothing
End Sub

 

[CT Witter]

This can also be done if SQL. My Table is "Table2"
Fields
ID1=Autonum
ID1,ID,Value

SELECT Table2.ID1, (Select Max(ID) from Table2 as X  WHERE X.Id1 <= Table2.ID1 order by  Table2.ID) AS IDFill, (Select Max(Value) from Table2 as X  WHERE X.Id1 <= Table2.ID1 order by  Table2.ID) AS ValueFill
FROM Table2;

Yields:
----------------------------------------------------------------
| ID1 | IDFill | ValueFill |
----------------------------------------------------------------
| 1 | 1 | 35010 |
----------------------------------------------------------------
| 2 | 1 | 35010 |
----------------------------------------------------------------
| 3 | 1 | 35010 |
----------------------------------------------------------------
| 4 | 2 | 35015 |
----------------------------------------------------------------
| 5 | 2 | 35015 |
----------------------------------------------------------------
| 6 | 2 | 35015 |
----------------------------------------------------------------

HTH,
CT

 

[elbastador]

parry:

Thanks for that code. That's essentially what I was thinking would have to be done, but I didn't know how to do it. Only problem is that on this line

"If Rs!repaired_By = "" Or IsNull(Rs!repaired_By) Then"

I get a Runtime error 3265: Item not found in this collection.

I checked and I have Microsoft DAO 3.6 Object Library enabled. Is there something else I need to run this?

Thanks!

 

[CT Witter]

You need to change "repaired_By" to whatever your field is called in your table.

HTH,
CT

 

[elbastador]

Completely missed that. Thanks! It works perfectly.

 

[navafolk]

Hi guys,
Please show me how to do the same thing if there is no ID column. Many thanks