Please help with a Loop

[lakers8175]

I have a table that has Division number then about 3, 4 or 5 nulls under it until the next divsion number. I want to take the division number and put it where the nulls are until the next division number. I want to continue this untill the last record of the table. Here is an example..
100 Div
null
null
null
200 Div
null
null
null
null
I would like it to look like
100 Div
100 Div
100 Div
100 Div
200 Div
200 Div
200 Div
200 Div
200 Div

Any help would be appreciated...
Sean

 

[grnzbra]

In your sub, add a variable called nzHold. As you loop through the recordset, when you hit a record that has a value in the division number, set nzHold equal to that number. Then, for each null, set the division number to the nzHold value. the if statement would look something like this

If IsNull(DivisionNumber) Then
DivisionNumber = nzHold
Else
nzHold = DivisionNumber
End If

 

[jfischer]

Assuming that you have an unordered (arrival sequence) table, the following VBA function and SQL query should do the job for you.

1) Add the following function to a Module.

Function DivNum(sDivNum As String) As String
  Static sCurrDiv As String

  If sDivNum & &quot;&quot; <> &quot;&quot; Then  'Update current division
    sCurrDiv = sDivNum
  End If
  DivNum = sCurrDiv
End If

2) Add an Update Query that uses the following SQL statement (substitute your own table and field names).

UPDATE Table1 SET Division = DivNum([Division] & "");

The DivNum function will store and return the current Division number to the Update query.

 

[thornmastr]

This will do it for you.

Dim mask As String
Dim rsTable As Recordset

Set rsTable = CurrentDb.OpenRecordset("mytable", dbOpenTable)
With rsTable
.MoveFirst
While Not .EOF
.Edit
If Nz(InStr(!Item, "Div&quot, 0) > 0 Then
mask = !Item
Else
!Item = mask
End If
.Update
.MoveNext
Wend
.Close
End With
Set rsTable = Nothing
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com