Updating Fields?

[ncanals]

I have a table similar to this one:

Field1 field2 field3
1 Serial# 1
2 copy 1
3 .
4 .
5 Serial# 2
6 copy 2
7 .
8 .
. . .
. . .
. . .

How do I create a Query that takes a number/string in Field3 and copies the number/string to the next record if blank until it finds another number in the same field and copies the new number/string until it finds another number/string , etc.?

 

[llkhoutx]

I've seen a post that say use [FieldName]+1 to get to the next record, if this is true, I suppose that [FieldName]-1 would get you the previous record.

Personally, I would use a recordset of the table, loop through it with DAO code, always keeping the previous value of a particular field.

Feddema's DAO Object Model by O'Reilly (434.95 retail) is a great resource, as is Roman's Access Database, 2nd Edition by O'Rielly ($24.95 retail).

 

[Quehay]

It's been a long day, but I think this will do it. You're counting on the values that you want to transer being "earlier" in the recordset. Try it on a copy of the table not your data!!

Dim blnFoundValue as Boolean

Dim db as DAO.Database
Dim rs as DAO.Recordset

Dim varValue as ?

Set db = CurrentDB()
Set rs = db.OpenRecordset("YourTable&quot

With rs

.MoveFirst

While Not .EOF


.Edit

If not IsNull(!Field) Then
varValue = !Field
blnFoundValue = True

If IsNull(!Field) and blnFoundValue Then
!Field = varValue
End if
End If

.MoveNext

Loop

rs.Close

Exit_ErrorTrap:
set rs = nothing
set db = nothing
Exit Sub

ErrorTrap:
Msgbox etc.
Resume Exit_ErrorTrap