By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,030 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,030 IT Pros & Developers. It's quick & easy.

Filling null records with previous value--loop?

P: n/a
Hello--i have inherited a dataset that has records like below:
id locationid
1 7
2
3 5
4
5
6 4
7
I need to fill in the null fileds in location id with the value from the record immediately
preceding the null one. How can I loop through these records and sub the needed value in. Some will
have one null record and others will have many--so I want to hold the location id from the first
record--replace the nulls with it until I hit a record with a value in it--then hold the next value
through the next set of nulls, and so on. I am slightly familiar with loops but have not used them.
Any help would be appreciated

thanks
bob
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Bob, I got this piece of code from this news group a while back. Wish I
could remember whom exactly it came from so I could give credit where it is
due (sorry). Try something like this.

'Copies down a field to other records below
'Eg When getting data from a spreadsheet only the first row of a section may
be populated
'blue x
' y
' z
'green x
' y
'When each row should contain a colour

Function CopyFieldRecords(pstrRST As String, pstrField As String, pstrID As
String) As Boolean

Dim db As Database
Dim rec As Recordset
Dim vCopyDown As Variant

CopyFieldRecords = True

On Error GoTo err_copyrecords

vCopyDown = Null

Set db = CurrentDb()

Set rec = db.OpenRecordset("select * from [" & pstrRST & "] order by [" &
pstrID & "]")
While Not rec.EOF
'If the field isn't blank then use this to copy down
If Nz(rec(pstrField), "") <> "" Then
vCopyDown = rec(pstrField)
Else
'Only if we have something to copy down
If Nz(vCopyDown, "") <> "" Then
rec.Edit
rec(pstrField) = vCopyDown
rec.Update
End If
End If
rec.MoveNext
Wend

exit_copyrecords:
Exit Function

err_copyrecords:
MsgBox Error, vbCritical, "Copy Fields Down Records"
CopyFieldRecords = False
GoTo exit_copyrecords

End Function

'Call the function like this
pstrRST = "YourQueryName" 'or table
pstrFieldToCopy = "YourFieldName" 'name of the field that you want to
copy down
pstrID = "IDField"
Call CopyFieldRecords(pstrRST, pstrFieldToCopy, pstrID)
I changed some things to fit your request and haven't actually tested the
above code, but it shoud do the trick.
--
Reggie

----------
<al*****@cox.net> wrote in message
news:40***************@news.west.cox.net...
Hello--i have inherited a dataset that has records like below:
id locationid
1 7
2
3 5
4
5
6 4
7
I need to fill in the null fileds in location id with the value from the record immediately preceding the null one. How can I loop through these records and sub the needed value in. Some will have one null record and others will have many--so I want to hold the location id from the first record--replace the nulls with it until I hit a record with a value in it--then hold the next value through the next set of nulls, and so on. I am slightly familiar with loops but have not used them. Any help would be appreciated

thanks
bob

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.