Howto copy data from previous line and column if a field is blank

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing data. In the first record it contains data in field 1 & 2. The
next several rows only has data in field 2. I need to have the data in field
1, the same as the 1st record. Several reocrds later, field 1 changes and I
need to be able to change the data at that point to reflect the new value.
Thanks
 
The problem here is that once you get the data into an Access table,
notions such as "first record", "second record" and "several records
later" can only be relied on if you can sort the records into that
specific order on the basis of the data they contain. Even then, you
have to jump through hoops to insert the missing values.

So in general it's best to insert the missing values before you import
the data, to write VBA code that imports the records and inserts the
values as it does so.

But if you've imported the table and when you look at it in datasheet
view it's in the correct order, an update query using this little VBA
function has a reasonable chance of doing what you want:

Public Function SupplyMissingValue(V As Variant) As Variant
Static Stored As Variant

If Not IsNull(V) Then
Stored = V
End If
SupplyMissingValue = Stored
End Function

The query will be something like this:

UPDATE MyTable SET TheField = SupplyMissingValue([TheField])

If you need more, post back here.
 
Back
Top