Replicate missing data into null entries

[octocom]

Hi there

I have some a little programing in microsoft access vb and so I am here asking for advice.

I need to copy a data value from one field down the list of null entries till it reaches the next value and then use that an so on in a loop.

E.G
[Area Code]
199a
null
null
null
null
132f
null
null
null

Regards

Luke

 

[RuralGuy]

What is your sort order? How do you keep these records in this order?

 

[octocom]

Hi Ruralguy

Its in order of ID and a id2 key fields the reason being this database has been
extracted from a mulitivalue database and imported to access but because of this I have gone from 87,000 records to over 1,000,000 records because the data has been seperated so one record may have say 20 weeks of information. so I need to copy say area "a32" down into the null fileds to it reaches next value once this is done I can group records based on change of area and week.

eg null=same data till next entrie

[id] [id2] [Name] [area] [week] [payment]

1 1 john a32 91/03 20.30
2 1 null null 91/04 20.30
3 1 null null 91/04 23.30
4 1 null ac3 91/05 null
5 2 james bc3 95/09 25.50
6 2 null null 95/10 null

Regards

Luke

 

[RuralGuy]

Hi Luke,

I think something like the following should fill the bill.
<<< WARNING AIR CODE >>>

Public Sub FillDown()

On Error GoTo Err_FillDown

'-- Change this constant if there are more fields
Const FieldCount As Integer = 4

'-- This uses DAO and requires a reference to that Object Library
Dim MyRs As DAO.Recordset
Dim FillData(FieldCount) As Variant
Dim FieldIndex As Integer

Set MyRs = CurrentDb().OpenRecordset("[b]YourTableName[/b]", dbOpenDynaset)

With MyRs
   Do While Not .EOF
      For FieldIndex = 0 To FieldCount - 1
         If IsNull(.Fields(FieldIndex)) Then
            If Len(FillData(FieldIndex) & "") > 0 Then
               .Fields(FieldIndex) = FillData(FieldIndex)
            Else
               '-- Just report the problem
               MsgBox "No previous data for " & .Fields(FieldIndex).Name
               '-- and leave everything alone
            End If
         Else
            FillData(FieldIndex) = .Fields(FieldIndex)
         End If
      Next FieldIndex
      .MoveNext
   Loop
End With


Exit_FillDown:
   On Error GoTo 0
   MyRs.Close
   Set MyRs = Nothing
   Exit Sub

Err_FillDown:
   MsgBox "Error No:    " & Err.Number & vbCr & _
   "Description: " & Err.Description
   Resume Exit_FillDown

End Sub

You just need to replace YourTableName with your info. You can put it in a standard module and call it from the immediate window.

 

[octocom]

Hi RuralGuy

Thanks for helping me out.

Regards

 

[RuralGuy]

You're certainly welcome.